Export Import oracle schema with data pump

We can export single as well as multiple schema’s/users with the help of SCHEMAS data pump parameter.

Consider following hands-on on exporting schema/user with the help of data pump utility:

Note:
If EXP_FULL_DATABASE role not granted to user who performing export operation, then user can only perform export his schema not others. (default)

 

Here we are exporting scott schema ( Single schema ) from orcl database:

expdp system/manager schemas=scott directory=data_pump_bkup dumpfile=scott_export.dmp logfile=scott_export.log

. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/data_pump_bkup/scott_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 11:58:35
Importing above exported schema(scott):
impdp system/manager schemas=scott directory=data_pump_bkup dumpfile=scott_export.dmp logfile=scott_import.log
–x–

 

we can also export multiple schema’s with the help of same SCHEMAS data pump parameter.

In following case, we are exporting scott & test schema’s from orcl database;

expdp system/manager schemas=scott,test directory=data_pump_bkup dumpfile=scott_test_export.dmp logfile=scott_test_export.log

. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
. . exported “TEST”.”DEPT” 0 KB 0 rows
. . exported “TEST”.”EMP” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/data_pump_bkup/scott_test_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 12:07:36
Importing above exported schemas(scott & test):
impdp system/manager schemas=scott,test directory=data_pump_bkup dumpfile=scott_test_export.dmp logfile=scott_test_import.log

. . imported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . imported “SCOTT”.”EMP” 8.570 KB 14 rows
. . imported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . imported “SCOTT”.”BONUS” 0 KB 0 rows
. . imported “TEST”.”DEPT” 0 KB 0 rows
. . imported “TEST”.”EMP” 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 12:09:06
–x–

Likewise we can Export-Import multiple schema’s with the help of data pump utility.

************************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

2 thoughts on “Export Import oracle schema with data pump”

  1. Hi need the command for the below scenario:

    Source version : 12.1.0.2.0

    Target Version: 11.2.0.4

    Need to Export a schema from source having 100 tables and we need the same schema to be imported to the target with only 25 tables

    • Thank you Hadley for writing.
      Try following data pump command, this will help you to export schema from oracle 12c and than import it to the oracle 11g. After successful import you can delete unwanted 75 tables from oracle 11g database.
      expdp user_name/password directory=dir_name dumpfile=dump_file_name.dmp logfile=log_file_name.log schemas=schema_name version=11.2

      OR you can specify that 25 tables that you want to export and import to 11g.
      expdp user_name/password directory=dir_name dumpfile=dump_file_name.dmp logfile=log_file_name.log tables=scott.table1,scott.table2,... version=11.2

      Hope this information will be helpful to you.

Leave a Reply