Topic : Oracle Data Pump

What is use of CONSISTENT option in exp?


Answer :

Cross-table consistency. Implements SET TRANSACTION READ ONLY.
Default value N.

Show Answer Discuss

What is use of DIRECT=Y option in exp?


Answer :

Setting direct=yes; To extract data by reading the data directly; bypasses the SGA; bypassing the SQL command-processing layer (evaluating buffer); so it should be faster.
Default value N.

Show Answer Discuss

What is use of COMPRESS option in exp?


Answer :

Imports into one extent. Specifies how export will manage the initial extent for the table data. This parameter is helpful during database re-organization. Export the objects (especially tables and indexes) with COMPRESS=Y.
If table was spawning 20 Extents of 1M each (which is not desirable; taking into account performance); if you export the table with COMPRESS=Y; the DDL generated will have initial of 20M.
Later on when importing the extents will be coalesced. Sometime it is found desirable to export with COMPRESS=N; in situations where you do not have contiguous space on disk (tablespace); and do not want imports to fail.

Show Answer Discuss

How to improve exp performance?


Answer :

1. Set the BUFFER parameter to a high value. Default is 256KB.
2. Stop unnecessary applications to free the resources.
3. If you are running multiple sessions; make sure they write to different disks.
4. Do not export to NFS (Network File Share). Exporting to disk is faster.
5. Set the RECORDLENGTH parameter to a high value.
6. Use DIRECT=yes (direct mode export).

Show Answer Discuss

How to improve imp performance?


Answer :


1. Place the file to be imported in separate disk from datafiles.
2. Increase the DB_CACHE_SIZE.
3. Set LOG_BUFFER to big size.
4. Stop redolog archiving; if possible.
5. Use COMMIT=n; if possible.
6. Set the BUFFER parameter to a high value. Default is 256KB.
7. It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
8. Use STATISTICS=NONE
9. Disable the INSERT triggers; as they fire during import.
10. Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.

Show Answer Discuss