Monday , September 25 2017
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

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

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