Useful EXPDP Commands

Here you can find some useful EXPDP commands:

1) Export FULL database:

    expdp system/manager dumpfile=full.dmp directory=DATA_PUMP_DIR full=y logfile=full.log

2) Export database with multiple dump files:

     In some cases where the Database is in Terabytes and since the dump file size will be larger than the operating system limit, and hence export will fail. In those situations you can create multiple dump files by typing the following command.

     expdp  system/manager FULL=Y DIRECTORY=DATA_PUMP_DIR DUMPFILE=full%U.dmp FILESIZE=5G  LOGFILE=myfullexp.log JOB_NAME=myfullJob
   
     Note: This will create multiple dump files named full01.dmp, full02.dmp, full03.dmp and so on. The FILESIZE parameter specifies how large the dump file should be.

3) Export schema:

     Below command will useful for taking backup of single schema (SCOTT) with using SCOTT credentials:

     expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=scottexp.dmp logfile=scottexp.log

4) Export multiple schemas:

     Below command for taking backup of multiple schemas.

     expdp system/manager dumpfile=scott_demo.dmp directory=DATA_PUMP_DIR schemas=(scott,demo) logfile=scottexp.log

5) Export Table: 

     Below command for taking backup of single table (emp) under scott schema

     expdp scott/tiger dumpfile=scott_emp.dmp directory=DATA_PUMP_DIR tables=(emp) logfile=scottexp.log

6) Export multiple tables:

    Below command for taking backup of multiple tables (emp,dept) under scott schema

    expdp scott/tiger dumpfile=scott_emp_dept.dmp directory=DATA_PUMP_DIR tables=(emp,dept) logfile=scottexp.log

7) Export Tablespaces:

  Below command for taking backup of tablespace (users)
 
    expdp system/manager dumpfile=users_ts.dmp directory=DATA_PUMP_DIR tablespaces=(users) logfile=users_ts.log

8) Export multiple tablespaces:

     Below command for taking backup of multiple tablespaces (users, hr)

    expdp system/manager dumpfile=users_ts.dmp directory=DATA_PUMP_DIR tablespaces=(users, hr) logfile=users_ts.log

9) Export table with data only:

     Below command for taking single table with data only
 
     expdp scott/tiger dumpfile=emp_data.dmp directory=DATA_PUMP_DIR tables=(emp) content=data_only logfile=emp_data.log

10) Export table with metadata only:

       Below command for taking single table with metadata only.
 
       expdp scott/tiger dumpfile=emp_metadata.sql directory=DATA_PUMP_DIR tables=(emp) content=metadata_only logfile=emp_metadata.log

11) Export Full database without logfile:

      expdp system/manager dumpfile=full.dmp directory=DATA_PUMP_DIR full=y nologfile=y

Estimate parameter: 

This parameter will tell us how much space a new export job is going to consume. the space estimation is always in terms of bytes. we can specify the database to provide us with estimates using either number of database blocks or optimizer statistics.

12) Export with parameter estimate (blocks and statistics)

       expdp scott/tiger dumpfile=scott_estimate.dmp directory=data_pump_dir  logfile=scott_estimate.log estimate=blocks

       expdp scott/tiger dumpfile=scott_estimate.dmp directory=data_pump_dir  logfile=scott_estimate.log  estimate=statistics

INCLUDE and EXCLUDE parameters:

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.

13) Export with EXCLUDE and INCLUDE examples:

       Below command for excluding table EMP in SCOTT schema.

       expdp system/manager dumpfile=scott_1.dmp directory=data_pump_dir logfile=scott_1.log schemas=scott exclude=table:"in('EMP')"

       Below command for excluding database objetcs with table EMP in SCOTT schema.

expdp system/manager dumpfile=scott_2.dmp directory=data_pump_dir logfile=scott_2.log schemas=scott exclude=procedure,trigger,function,sequence,index,table:"in('EMP')"

Below command for excluding table which starts with T under SCOTT schema.

expdp scott/tiger directory=data_pump_dir dumpfile=scott_schema.dmp logfile=scott_3.log schemas=scott exclude=table:"like'T%'"

Below command for including table which starts with S under SCOTT schema.

expdp scott/tiger directory=data_pump_dir dumpfile=scott_schema1.dmp logfile=scott_4.log schemas=scott include=table:"like'S%'"

14) Export with QUERY Option: Predicate clause used to export a subset of a table.

       Below command will useful for export only pirticular records with using QUERY option

expdp scott/tiger QUERY=emp:'"WHERE deptno = 10 AND sal > 10000"' DIRECTORY=data_pump_dir DUMPFILE=exp1.dmp logfile=scott_5.log
 
Go through with my previous articles related to Datapump:

impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037
ORA-31685 Error while import using IMPDP
Script: Shell Script to export Full DB or Single or Multiple Schemas
Export Logical Database Backup Dumps to ASM Disk

Hope this article will help you.  :-)

Thanks,
Chowdari