Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Location
    Salem, Oregon
    Posts
    19

    Unanswered: Full Export Selected Imports

    Does anyone have a procedure for getting Consistent data using Oracle exports. IE. take table offline or make read only while doing export then taking it back online or making it read-write. We are trying to be able to recover at the table, tablespace and schema level if a user creates errors in the data.

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Your answer is found in your question... the parameter CONSISTENT=Y will tell the export command to create a read-consistent image of the table that is being exported.

    JoeB

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Consistant=Y as stated, is what you are looking for... Keep in mind that for the duration of the export, the rollback segments will be increasing and they will have to be scanned for uncommitted trxs.... If possible, export groups of tables together in separate export runs (if that makes sense in your environment). Exports will run longer and be wary of "Snapshot too old" possibilities...

    Gregg

  4. #4
    Join Date
    Apr 2004
    Location
    Salem, Oregon
    Posts
    19

    snapshot too old possibilities

    What is the meaning of snapshot too old possibilities. Does that mean if the export takes to long the Data is not consistent with the other related tables

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    visit http://asktom.oracle.com
    do KEYWORD search on ORA-01555
    read & understand what Tom Kyte has written on this error & decide how best to avoid this error in the future for your environment.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    good call Anacedent ... Tom explains it best !!!

  7. #7
    Join Date
    Apr 2004
    Location
    Salem, Oregon
    Posts
    19
    So the tables dont have to read only or offline and they can be available for select, update, and deletes and As long as I have consistent=Y I will be fine?

    Thanks for Your help

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    That is correct ... the export will get a "CONSISTANT" view of the data
    at the point in time the export begins.

    Gregg

  9. #9
    Join Date
    Apr 2004
    Location
    Salem, Oregon
    Posts
    19
    Does the snapshot to old apply to Oracle 10g with its auto tunning capabilites? I have only the Oracle 9i books.
    Thanks again

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Yes, Snapshot too old is typically caused by the following.

    When a long running query is run, Oracle, to maintain data constincy for that query will write changes to it's redo logs. If all its redo logs are filled up before the query has finished, the query will get a snapshot too old message and will be flushed so that the database can switch to a fresh log. The fix is to make your redo logs bigger, so that they will last longer.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Apr 2004
    Location
    Salem, Oregon
    Posts
    19
    In Oracle 10g there is not a parameter for CONSISTENT
    Its a parameter comparablt to Consistent not needed. It also says Use FLASHBACK_SCN and FLASHBACK_TIME for this funtionality.
    What does that mean to you?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    When all else fails, Read The Fine Help!

    Z:\>exp help=yes

    Export: Release 10.1.0.2.0 - Production on Wed Sep 22 15:15:49 2004

    Copyright (c) 1982, 2004, Oracle. All rights reserved.



    You can let Export prompt you for parameters by entering the EXP
    command followed by your username/password:

    Example: EXP SCOTT/TIGER

    Or, you can control how Export runs by entering the EXP command followed
    by various arguments. To specify parameters, you use keywords:

    Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
    Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
    or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

    USERID must be the first parameter on the command line.

    Keyword Description (Default) Keyword Description (Default)
    --------------------------------------------------------------------------
    USERID username/password FULL export entire file (N)
    BUFFER size of data buffer OWNER list of owner usernames
    FILE output files (EXPDAT.DMP) TABLES list of table names
    COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
    GRANTS export grants (Y) INCTYPE incremental export type
    INDEXES export indexes (Y) RECORD track incr. export (Y)
    DIRECT direct path (N) TRIGGERS export triggers (Y)
    LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
    ROWS export data rows (Y) PARFILE parameter filename
    CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

    OBJECT_CONSISTENT transaction set to read only during object export (N)
    FEEDBACK display progress every x rows (0)
    FILESIZE maximum size of each dump file
    FLASHBACK_SCN SCN used to set session snapshot back to
    FLASHBACK_TIME time used to get the SCN closest to the specified time
    QUERY select clause used to export a subset of a table
    RESUMABLE suspend when a space related error is encountered(N)
    RESUMABLE_NAME text string used to identify resumable statement
    RESUMABLE_TIMEOUT wait time for RESUMABLE
    TTS_FULL_CHECK perform full or partial dependency check for TTS
    TABLESPACES list of tablespaces to export
    TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
    TEMPLATE template name which invokes iAS mode export

    Export terminated successfully without warnings.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •