In the first blog post – How to get the DDL of a database object with dbms_metadata – I described one way to export DDLs of database objects. This first approach is handy for single objects. But if you want to extract the DDL statements of all objects, there is an easier way. You can use Oracle data pump for this.

In the old days, there was the exp/imp tool by Oracle which exported the whole database/schema/table, depending on the parameters you defined for the program. It produced SQL statements for your database.

The data pump utility was introduced with Oracle 10g. It provided a new way of dumping database objects. The way the export is saved is not SQL statements anymore; it is a proprietary binary file format. Additionally, the export dump file is saved on the server and not on the client anymore. This file storage makes it harder for anyone interested in the DDL Statements of all database objects to get them.

To reach the goal of exporting DDL statements with data pump, we do have to use additional steps. We have to export the data. This export could include the whole database or only the objects we want the DDL statements.

Then we have to issue an import with the “SQL_FILE” parameter. This import provides a file with all the DDL statements defined in the impdp command. This import is not importing anything it is just providing the SQLFile.

In the next sections, I will show how this is done.

Exporting the data

As mentioned before, there are many possibilities on how to define what should be exported. One rule that applies is, you are only able to export database objects for which you have a view privilege.

This limitation can be avoided if you have the DATAPUMP_EXP_FULL_DATABASE role granted. With this role you are able to dump the full database with all the objects inside.

To define which objects you would like to export you could either use the EXCLUDE parameter or the INCLUDE parameter. If you are on a database version 21c you are able to use both (EXCLUDE and INCLUDE) in the same job. In previous versions, only one parameter can be used in the same job.

To prevent you from quoting all special characters in the command on the command line it’s suggested to use a parameter file and define the EXCLUDE or INCLUDE in this file.

The parameter CONTENT allows us to define what is exported. If we specify this parameter as METADATA_ONLY only the DDL statements are in the dump file. This decreases the size of the dump file and increases the export speed. For large databases this is very important.

The parameter file must be accessible by the expdp tool. If it is located in the same directory as you are executing the expdp tool, you do not have to specify the path. If the file is not located in the same directory you have to specify the name including the path of the file. I usually name the file with a meaningful filename that I, later on, know what I used it for. There are no rules for naming this file.

Here’s one example of a parameter file:

We are now able to execute the expdp command as follows:

The expdp tool is delivered with almost every Oracle database product, even with the Oracle Client. The only requirement for executing the expdp tool is that the machine where you are running this can connect to the database. If you do not have a machine which has access to the database, you can use the DBMS_DATAPUMP PL/SQL package.

Creating the SQLFILE

As soon we have the dump file of the database objects we defined we can extract the DDL statements out of it to a SQLFILE.

To create the requested SQLFILE, we could create a parameter file again for the import.

We are now able to execute the impdp command as follows:

Now we do have a file demo_ddl.sql located in the directory EXPORT_DIRECTORY with the DDL of our database objects.


If we need to export the DDL statements of more than one object, expdp/impdp is an easy way. With this utility, it is possible to create DDL statements for all objects we have the required privileges. The downside is, to use data pump, we do need a directory on the server and a process to copy files from this folder.