Home / Oracle DBA / ORACLE EXPORT & IMPORT

ORACLE EXPORT & IMPORT

ORACLE EXPORT & IMPORT

  1. It is the utility to transfer the data between two oracle databases.
  2. The following levels of export/import are possible
    1. Database level
    2. Schema level
    3. Table level
    4. Row level
  3. Apart from database level, we can perform other levels of export and import within the same database.
  4. Whenever session is running long time, we can check from v$session_longops.
Note: To avoid questionable statistics warning, use statistics=none during export.
  1. Export will convert the command to select statements and the final output will be returned to dumpfile.
exp→select statement →datafiles→ DBC →dumpfile
  1. Server process will take the responsibility of writing the data to dumpfile.
  2. Export will transfer the data to dumpfile in the size of block. To increase the speed of writing we can set BUFFER=10 * avg row length. But we will never use this formula in real time.
Note: avg row length can be obtained from dba_tables.
  1. DIRECT=Y will make the export process faster by performing in the following way.
exp→ select statement →datafiles→dumpfile
  1. DIRECT=Y is not applicable for
    1. A table with LONG datatype
    2. A table with LOB datatype
    3. Cluster table
    4. Partitioned table
Note: when we give direct=y option, if oracle cannot export a table with that option, it will automatically convert to conventional path.
  1. By mentioning CONSISTENT=Y, export will take data from only undo tablespace if a DML operation is being performed on the table.
Note : while using CONSISTENT=Y, there is a chance of getting ORA-1555 error.
  1. Import is the utility to dump the contents from export dumpfile to a schema.
  2. Import internally converts contents of export dump file to DDL and DML statements.
imp→ create table → inserts the data → create index or other objects → add constraints and enable them
  1. SHOW=Y can be used to check corruption in export dump file. This will not actually import the contents.
  2. IGNORE=Y should be used if already an object exists with the same name. It will append the data if the object exists already.
Note: whenever import fails with warning for constraints or grants, do import again with ROWS=N option.
Note: when we are importing tables with LONG, LOB datatypes or partitioned tables, the destination database should also contain same tablespace name as source database.

COMMANDS

# To know options of export/import
[oracle@server1 ~]$exp help=y
[oracle@server1 ~]$ imp help=y
# To take database level export
[oracle@server1 ~]$ exp file=/u01/fullbkp_prod.dmp log=/u01/fullbkp_prod.log full=y
# To take schema level export
[oracle@server1 ~]$ exp file=/u01/scott_bkp.dmp log=/u01/scott_bkp.log owner=’SCOTT’
# To take table level export
[oracle@server1 ~]$ exp file=/u01/emp_bkp.dmp log=/u01/emp_bkp.log tables=’SCOTT.EMP’
# To take row level export
[oracle@server1 ~]$ exp file=/u01/emp_rows_bkp.dmp log=/u01/emp_rows.log tables=’SCOTT.EMP’ query=\”where deptno=10\”
# To import full database
[oracle@server1 ~]$ imp file=/u01/fullprod.dmp log=/u01/imp_fullprod.log full=y
# To import a schema
[oracle@server1 ~]$ imp file=/u01/scott_bkp.dmp log=/u01/imp_schema.log fromuser=’SCOTT’ touser=’SCOTT’
# To import a table
[oracle@server1 ~]$ imp file=/u01/emp_bkp.dmp log=/u01/imp_emp.log fromuser=’SCOTT’ touser=’SCOTT’ tables=’EMP’
# To import a table to another user
[oracle@server1 ~]$ imp file=/u01/emp_bkp.dmp log=/u01/imp_emp.log fromuser=’SCOTT’ touser=’SYSTEM’ tables=’EMP’
Read More:
Oracle Distributed Database Management
ORACLE PASSWORD FILE (PFILE)
Creating new Listener manually
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe