Generating DDL of a schema using datapump

Here is the method to get entire DDL of a schema in SQL using datapump. In this example, we are trying to get DDL of a schema called MYSCHEMA.

Export entire schema metadata into a dump file. It will be created in directory MYDIR. To this work, first there should be a directory object MYDIR in database.

expdp murty@mydb schemas=MYSCHEMA directory=MYDIR dumpfile=schema_metadata.dmp content=METADATA_ONLY

Now we can extract SQL from the dump file like below.

impdp murty@mydb directory=MYDIR dumpfile=schema_metadata.dmp sqlfile=schema_metadata.sql exclude=STATISTICS