Export Import oracle tables with Data Pump utility

We can export single as well as multiple tables with the help of TABLES data pump parameter.

Consider following hands-on on exporting tables with the help of data pump utility.

//Consider example on single table export, in this we are exporting table emp from scott schema.

expdp system/manager tables=scott.emp directory=data_pump_bkup dumpfile=emp_export.dmp logfile=emp_export.log
. .. …
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /home/oracle/app/data_pump_bkup/emp_export.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at 06:29:14

 

//If you wish to export partial table rows then we can add WHERE clause to our export.
Consider example below, here we exporting only those rows from scott.emp table where emp.hiredate is greater then ’17-DEC-80′:
expdp system/manager tables=scott.emp query=scott.emp:where emp.HIREDATE >= ’17-DEC-80′ directory=data_pump_bkup dumpfile=emp_partial_export.dmp logfile=emp_export.log
–x–

 

Importing above exported table(emp):
impdp system/manager tables=scott.emp directory=data_pump_bkup dumpfile=emp_export.dmp logfile=emp_import.log

. . imported “SCOTT”.”EMP” 8.570 KB 14 rows

Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at 09:24:10
–x–

 

We can also export multiple tables with the help of same TABLES data pump parameter. In following case, we are exporting emp & dept table from scott schema.

expdp system/manager tables=scott.emp,scott.dept directory=data_pump_bkup dumpfile=emp_dept_export.dmp logfile=emp_dept_export.log
. .. …
. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/data_pump_bkup/emp_dept_export.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at 08:53:22

 

Importing above exported tables(emp,dept):
impdp system/manager tables=scott.emp,scott.dept directory=data_pump_bkup dumpfile=emp_dept_export.dmp logfile=emp_dept_import.log

. . imported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . imported “SCOTT”.”EMP” 8.570 KB 14 rows

Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at 09:27:03
–x–

Likewise we can Export-Import multiple tables 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.

Leave a Reply