Generating DDL script for a schema by using impdp

SQLFILE parameter in impdp can be used to generate the DDLs from a dumpfile.

If you need the DDL of all schema’s objects , then use SQLFILE with IMPDP command as below.

This will not do the actual import. It will just dump the metadata (DDL) of the schema in the specified .sql file.

Export :

expdp \"/ as sysdba\" directory=METADATA_EXP_DIR dumpfile=USER01_METADATA_`date +"%Y%m%d"`.DMP logfile=USER01_METADATA_`date +"%Y%m%d"`.LOG schemas=USER01 content=metadata_only cluster=N

Import :

impdp \"/ as sysdba\" directory=METADATA_EXP_DIR dumpfile=USER01_METADATA_`date +"%Y%m%d"`.DMP logfile=USER01_METADATA_IMP_`date +"%Y%m%d"`.LOG sqlfile=USER01_METADATA_`date +"%Y%m%d"`.SQL

If you need to change the schema name , you can use remap_schema parameter as below.

impdp \"/ as sysdba\" directory=METADATA_EXP_DIR dumpfile=USER01_METADATA_`date +"%Y%m%d"`.DMP logfile=USER01_METADATA_IMP_`date +"%Y%m%d"`.LOG sqlfile=USER01_METADATA_`date +"%Y%m%d"`.SQL remap_schema=USER01:USER02