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:
|
1 2 3 4 5 |
INCLUDE=TABLE:"IN ('DEMOTABLE')" DIRECTORY=EXPORT_DIRECTORY DUMPFILE=demo_sql.dmp LOGFILE=demo_sql.log CONTENT=METADATA_ONLY |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
expdp demouser/demopassword PARFILE=exportparams.par Export: Release 19.0.0.0.0 - Production on Thu Dec 17 10:19:13 2020 Version 19.5.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "DEMOUSER"."SYS_EXPORT_SCHEMA_01": demouser/******** PARFILE=exportparams.par Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/TABLE Master table "DEMOUSER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for DEMOUSER.SYS_EXPORT_SCHEMA_01 is: /tmp/demo_sql.dmp Job "DEMOUSER"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 17 10:19:20 2020 elapsed 0 00:00:05 |
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.
|
1 2 3 4 5 |
FULL=YES SQLFILE=demo_ddl.sql DIRECTORY=EXPORT_DIRECTORY DUMPFILE=expdemo_sql.dmp LOGFILE=expdemo_sql.log |
We are now able to execute the impdp command as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
impdp demouser/demopassword PARFILE=impdpparams.par Import: Release 19.0.0.0.0 - Production on Wed Dec 9 15:24:40 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "DEMOUSER"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "DEMOUSER"."SYS_SQL_FILE_FULL_01": demouser/******** PARFILE=impdpparams.par Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "DEMOUSER"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Dec 9 15:24:42 2020 elapsed 0 00:00:02 |
Now we do have a file demo_ddl.sql located in the directory EXPORT_DIRECTORY with the DDL of our database objects.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- CONNECT DEMOUSER ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/TABLE/TABLE CREATE TABLE "DEMOUSER"."DEMOTABLE" ( "DEMONUMBER" NUMBER(1,0) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; -- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS -- fixup virtual columns... -- done fixup virtual columns |
Conclusion
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.
