Starting with Oracle 10g, Data Pump can be used to export and import database objects using command-line tools expdp and impdp. Calls of these tools are mapped to the Data Pump API within the database. This blog post will demonstrate how to generate DBMS_DATAPUMP calls based on an expdp/impdp execution.
Table of Contents
Call expdp/impdp
Data Pump allows tracing its execution using the TRACE parameter. With the help of this functionality, it is possible to see what is happening in the background. To show this I perform a schema export of the user scott.
The TRACE parameter accepts a 7 digit hexadecimal mask. In our case, we will use FF0B00. Further information about this parameter can be found in My Oracle Support note Export/Import DataPump Parameter TRACE – How to Diagnose Oracle Data Pump (Doc ID 286496.1).
$> expdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=scott.dmp LOGFILE=scott.log SCHEMAS=SCOTT REUSE_DUMPFILES=YES TRACE=FF0B00
Export: Release 19.0.0.0.0 - Production on Mon Mar 30 09:37:14 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
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=scott.dmp LOGFILE=scott.log SCHEMAS=SCOTT REUSE_DUMPFILES=YES TRACE=FF0B00
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "SCOTT"."LOANS" 1.902 MB 100000 rows
. . exported "SCOTT"."TEST_UNDO_POOL" 1.528 MB 38416 rows
. . exported "SCOTT"."MV_CAPABILITIES_TABLE" 9.835 KB 14 rows
. . exported "SCOTT"."EMP" 8.781 KB 14 rows
. . exported "SCOTT"."EMP_BACKUP" 8.789 KB 14 rows
. . exported "SCOTT"."EMP_DDL" 8.281 KB 14 rows
. . exported "SCOTT"."COLTEST" 6.437 KB 1 rows
. . exported "SCOTT"."BOOKS" 5.984 KB 2 rows
. . exported "SCOTT"."DEPT" 6.031 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.960 KB 5 rows
. . exported "SCOTT"."MV1" 5.921 KB 2 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."DISABLE_TEST" 0 KB 0 rows
. . exported "SCOTT"."EMP2" 0 KB 0 rows
. . exported "SCOTT"."LITTTES" 0 KB 0 rows
. . exported "SCOTT"."TIMEZONE_DATA" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u00/app/oracle/admin/TVDDB1_SITE1/dpdump/scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Mar 30 09:38:30 2020 elapsed 0 00:01:15
Analyze Trace File
The trace file is generated within the Automatic Diagnostic Repository (ADR) directory structure. Check parameter diagnostic_dest to get the ADR home location or query V$DIAG_INFO.
SQL> SELECT value FROM v$diag_info WHERE name = 'ADR Home';
VALUE
--------------------------------------------------
/u00/app/oracle/diag/rdbms/tvddb1_site1/TVDDB1
First, we change our current working directory to the trace subdirectory. Within this directory, we have to search for trace files with the naming ${ORACLE_SID}_ora_nnnnn.trc.
$> cd $ADR_HOME/rdbms/tvddb1_site1/TVDDB1/trace
$> ls -ltr ${ORACLE_SID}_ora_*.trc | tail -n 5
-rw-r----- 1 oracle asmadmin 3575 Mar 30 09:29 TVDDB1_ora_2749.trc
-rw-r----- 1 oracle asmadmin 1070 Mar 30 09:37 TVDDB1_ora_4239.trc
-rw-r----- 1 oracle asmadmin 1107 Mar 30 09:37 TVDDB1_ora_4263.trc
-rw-r----- 1 oracle asmadmin 62678 Mar 30 09:54 TVDDB1_ora_5368.trc
-rw-r----- 1 oracle asmadmin 51011 Mar 30 09:55 TVDDB1_ora_5457.trc
Normally the newest trace file is the file we are looking for. But sometimes we have to use an older one. The trace file contains a lot of additional information. To get the used procedure/function calls, the following grep command can be used.
$> grep -E "[0-9]+:( \*{3}.*call \*{3}| {3}[a-zA-Z0-9_\t ()]+ = .*)+" TVDDB1_ora_5653.trc
SHDW:09:57:15.636: *** OPEN call ***
SHDW:09:57:15.636: operation = EXPORT
SHDW:09:57:15.636: job_mode = schema
SHDW:09:57:15.636: remote_link =
SHDW:09:57:15.636: job_name =
SHDW:09:57:15.636: version =
SHDW:09:57:15.636: compression = 2
SHDW:09:57:15.636: ena_sec_roles = 0
SHDW:09:57:16.704: *** SET_PARAMETER call ***
SHDW:09:57:16.704: handle = 12
SHDW:09:57:16.704: name = trace
SHDW:09:57:16.705: final value = 16714496
SHDW:09:57:16.721: *** GET_STATUS (1220) procedure call ***
SHDW:09:57:16.721: handle = 12
SHDW:09:57:16.721: mask = 2
SHDW:09:57:16.721: timeout = 0
SHDW:09:57:16.743: *** ADD_FILE call ***
SHDW:09:57:16.743: handle = 12
SHDW:09:57:16.743: filename = scott.log
SHDW:09:57:16.743: directory = DATA_PUMP_DIR
SHDW:09:57:16.743: filesize =
SHDW:09:57:16.743: filetype = 3
SHDW:09:57:16.743: reusefile =
SHDW:09:57:16.810: *** LOG_ENTRY call ***
SHDW:09:57:16.810: handle = 12
SHDW:09:57:16.810: message =
SHDW:09:57:16.810: log_file_only = 1
...
SHDW:09:58:27.196: *** GET_STATUS (1220) procedure call ***
SHDW:09:58:27.196: handle = 12
SHDW:09:58:27.196: mask = 9
SHDW:09:58:27.196: timeout = -1
SHDW:09:58:27.198: *** GET_STATUS (1220) procedure call ***
SHDW:09:58:27.198: handle = 12
SHDW:09:58:27.198: mask = 9
SHDW:09:58:27.198: timeout = -1
SHDW:09:58:27.262: *** EOJ_SYNCH call ***
SHDW:09:58:27.262: handle = 12
SHDW:09:58:27.262: final_msg_flags = 3
SHDW:09:58:27.262: job_state = COMPLETING
SHDW:09:58:27.262: *** DETACH call ***
SHDW:09:58:27.262: handle = 12
SHDW:09:58:28.126: *** DETACH call ***
SHDW:09:58:28.126: handle = 12
The above output is only an excerpt. Calls for LOG_ENTRY and GET_STATUS are usually not of interest and can be filtered out.
Generate Script
With the extracted information we can build our DBMS_DATAPUMP script. To do this we have to check every “call block”. I use the first code block as an example.
SHDW:09:57:15.636: *** OPEN call ***
SHDW:09:57:15.636: operation = EXPORT
SHDW:09:57:15.636: job_mode = schema
SHDW:09:57:15.636: remote_link =
SHDW:09:57:15.636: job_name =
SHDW:09:57:15.636: version =
SHDW:09:57:15.636: compression = 2
SHDW:09:57:15.636: ena_sec_roles = 0
The first line shows the procedure/function which is called. In this example DBMS_DATAPUMP.OPEN is used. The next lines represent the parameters. After checking the documentation we can build the PL/SQL block.
DECLARE
v_handle NUMBER;
BEGIN
v_handle := DBMS_DATAPUMP.OPEN (
operation => 'EXPORT',
job_mode => 'schema',
remote_link => NULL,
job_name => NULL,
version => NULL,
compression => 2,
ena_sec_roles => 0
);
END;
/
Now we have to repeat the above steps for all procedure/function calls to complete our PL/SQL script.