Data Pump impdp expdp Full, Schema, Table Level Export / Import Syntax with Examples



First step is you have to create an OS level directory and database level directory for the export import activity. You can ignore this step if you have the directories available already.
You can verify the directory details in dba_directories data dictionary view.
D:\>mkdir export

D:\>cd export

D:\export>sqlplus / as sysdba

SQL> create directory exp_dir as 'd:\export';

Directory created.

Grant privilege to the user going invoke the export the database. Grant export, import, read & write privileges on the directory.

SQL> grant exp_full_database to test;

Grant succeeded.

SQL> grant imp_full_database to test;

Grant succeeded.

SQL> grant read, write on directory exp_dir to test;

Grant succeeded.
Full Database Export
expdp directory=exp_dir full=y dumpfile=exp_fulldb.dmp logfile=exp_fulldb.log
Schema Level Export
expdp directory=exp_dir schemas=scott,test dumpfile=exp_schemadb.dmp logfile=exp_schemadb.log
Table Level Export
expdp directory=exp_dir tables=scott.emp,test.bonus dumpfile=exp_table.dmp logfile=exp_table.log

Syntax and example for full database export using data pump.
Compression parameter is not a mandatory one. But it is advisable to take full database export using compression option which will reduce the dump file size.
Syntax
expdp directory=exp_dir full=y compression=all dumpfile=exp_fulldb.dmp logfile=exp_fulldb.log

Example
D:\>expdp directory=exp_dir full=y compression=all dumpfile=exp_fulldb.dmp logfi
le=exp_fulldb.log

Export: Release 11.2.0.1.0 - Production on Mon Feb 25 11:45:50 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba


;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA directory=exp_dir full=y compression=all dumpfile=exp_fulldb.dmp logfile=exp_fulldb.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 127.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
. . . . . .
. . . . . .
. . . . . .
. . . . . .
. . . . . .
. . . . . .
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
. . exported "TEST"."BONUS"                                  0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  D:\EXPORT\EXP_FULLDB.DMP
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 11:44:52

Syntax and example for schema level export using data pump
For taking the schema level backup you have to use the schemas parameter with the data pump. You can specify more than one schema by separating each with comma (,).
Syntax
expdp directory=exp_dir schemas=scott,test compression=all dumpfile=exp_schemadb.dmp logfile=exp_schemadb.log
Example
D:\>expdp directory=exp_dir schemas=scott,test compression=all dumpfile=exp_sche
madb.dmp logfile=exp_schemadb.log

Export: Release 11.2.0.1.0 - Production on Mon Feb 25 12:27:23 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: test
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** directory=exp_dir schemas
=scott,test compression=all dumpfile=exp_schemadb.dmp logfile=exp_schemadb.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
. . . . .
. . . . .
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              4.976 KB       4 rows
. . exported "SCOTT"."EMP"                               5.664 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          4.898 KB       5 rows
. . exported "TEST"."DEPT"                               4.976 KB       4 rows
. . exported "TEST"."EMP"                                5.593 KB      14 rows
. . exported "TEST"."SALGRADE"                           4.890 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "TEST"."BONUS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  D:\EXPORT\EXP_SCHEMADB.DMP
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:28:27


Syntax and example for table level export using data pump
For table level export you can use the parameter tables and you can specify the tables from multiple schemas which are separated by comma.
D:\>expdp directory=exp_dir tables=scott.emp,test.bonus compression=all dumpfile
=exp_table.dmp logfile=exp_table.log

Export: Release 11.2.0.1.0 - Production on Mon Feb 25 12:31:08 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: test
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=exp_dir tables=s
cott.emp,test.bonus compression=all dumpfile=exp_table.dmp logfile=exp_table.log

Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               5.664 KB      14 rows
. . exported "TEST"."BONUS"                                  0 KB       0 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  D:\EXPORT\EXP_TABLE.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 12:31:45

How to recover database from Full database dump using impdp?
Method 1:
If you have same kind of file system available on the target system you can proceed with following steps. If you have different files systems available on the target system, you have to create the tablespaces before importing the dump.
Step 1.
          Create a general purpose database using the dbca.
Step 2.
          Import the full database dump using table_exists_action=skip option. This parameter will skip all the objects already exist in the database. So, whichever schema is available as new in the export dump will get import to the database.
impdp directory=exp_dir full=y dumpfile=exp_fulldb.dmp logfile=imp_fulldb.log table_exists_action=skip

Method 2:
If you know the important/required application schemas in the source database, then you can use the schemas parameter during import instead of going for the full import. This method will not try to import the default system schemas like sys, system etc. Like in method 1, If you have different files systems available on the target system, you have to create the tablespaces before importing the dump.
Step1.
          Create a general purpose database using the dbca.
Step 2.
impdp directory=exp_dir schemas=app_schema,testschema dumpfile=exp_fulldb.dmp logfile=imp_schemadb.log

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google