Export and import multiple schema using expdp/impdp (Data Pump utility)

Use the below sql query to export and import multiple schema:

expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log

impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log

sql query to export and import a schema:

expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log

impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log

Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both.

To export meta data only to get ddl of the schemas:

expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics

To get the DDL in a text file:

impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfile=content120514.dat

You can open the sql.dat file in a text format to see the entire DDL of the schema exported.

To check the DATA_PUMP_DIR path:

To see the DATA_PUMP_DIR location on server, use below sql query

set lines 300
col DIRECTORY_PATH for a50
select * from dba_directories where directory_name='DATA_PUMP_DIR';

To change the directory path:

connect database as sysdba and give following query

CREATE OR REPLACE DIRECTORY test_dir AS '/backup_DB/';

For example:

expdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log

impdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log