Automating DataPump Export

What’s the most elaborate thing you have done with DataPump?

So there I was, given the requirement to export multiple partitions for multiple tables where each partition has its own dump file having the format “tablename_partitionanme.dmp”, pondering how this can be done efficiently.

With the following metadata and requirements, what approach would you take?

If you are curious about the I approach I used, then read on.

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MDINH                          A_TAB                          P001
MDINH                          A_TAB                          P002
MDINH                          A_TAB                          P003
MDINH                          A_TAB                          P004
MDINH                          A_TAB                          P005
MDINH                          B_TAB                          P001
MDINH                          B_TAB                          P002
MDINH                          B_TAB                          P003
MDINH                          B_TAB                          P004
MDINH                          B_TAB                          P005

Here’s the demo:

$ nohup sqlplus "/ as sysdba" @exp_api.sql > exp_api.log 2>&1 &

$ cat exp_api.log
nohup: ignoring input

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 26 20:28:07 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@db01):PRIMARY> -- DataPump Export (EXPDP) Fails With Errors ORA-39001 ORA-39000 ORA-31641 ORA-27054 ORA-27037 When The Dump File Is On NFS Mount Point (Doc ID 1518979.1)
ARROW:(SYS@db01):PRIMARY> -- Work around for the above mentioned error
ARROW:(SYS@db01):PRIMARY> alter system set events '10298 trace name context forever, level 32';

System altered.

Elapsed: 00:00:00.00
ARROW:(SYS@db01):PRIMARY> declare
  2      h1 number;
  3      dir_name varchar2(30);
  4  begin
  5      dir_name := 'DPDIR';
  6      for x in (
  7          select table_owner, table_name, partition_name
  8          from   dba_tab_partitions
  9          where  table_owner = 'MDINH' and table_name in ('A_TAB','B_TAB') and regexp_like(partition_name,'[0-4]$')
 10          order  by table_owner, table_name, partition_position
 11      ) loop
 12
 13          h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE');
 14
 15          dbms_datapump.add_file (
 16              handle    => h1,
 17              filename  => x.table_name||'_'||x.partition_name||'.dmp',
 18              reusefile => 1,
 19              directory => dir_name,
 20              filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 21
 22          dbms_datapump.add_file (
 23              handle    => h1,
 24              filename  => 'exp_'||x.table_name||'_'||x.partition_name||'.log',
 25              directory => dir_name,
 26              filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 27
 28          dbms_datapump.set_parameter (
 29              handle => h1,
 30              name   => 'INCLUDE_METADATA',
 31              value  => 0);
 32
 33          dbms_datapump.metadata_filter (
 34              handle => h1,
 35              name   => 'SCHEMA_EXPR',
 36              value  => 'IN ('''||x.table_owner||''')');
 37
 38          dbms_datapump.metadata_filter (
 39              handle => h1,
 40              name   => 'NAME_EXPR',
 41              value  => 'IN ('''||x.table_name||''')');
 42
 43          dbms_datapump.data_filter (
 44              handle      => h1,
 45              name        => 'PARTITION_LIST',
 46              value       => x.partition_name,
 47              table_name  => x.table_name,
 48              schema_name => x.table_owner);
 49
 50          dbms_datapump.start_job (handle => h1);
 51          dbms_datapump.detach (handle => h1);
 52      end loop;
 53  end;
 54  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.92
ARROW:(SYS@db01):PRIMARY> alter system set events '10298 trace name context off';

System altered.

Elapsed: 00:00:00.00
ARROW:(SYS@db01):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Review export log:

$ ls -l exp*.log-rw-r--r--. 1 oracle oinstall 2888 Feb 26 20:28 exp_api.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P001.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P002.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P003.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_A_TAB_P004.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P001.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P002.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P003.log
-rw-r--r--. 1 oracle oinstall  578 Feb 26 20:28 exp_B_TAB_P004.log

Review export dump:

$ ls -l *.dmp
-rw-r-----. 1 oracle oinstall   90112 Feb 26 20:28 A_TAB_P001.dmp
-rw-r-----. 1 oracle oinstall   98304 Feb 26 20:28 A_TAB_P002.dmp
-rw-r-----. 1 oracle oinstall  188416 Feb 26 20:28 A_TAB_P003.dmp
-rw-r-----. 1 oracle oinstall 1069056 Feb 26 20:28 A_TAB_P004.dmp
-rw-r-----. 1 oracle oinstall   90112 Feb 26 20:28 B_TAB_P001.dmp
-rw-r-----. 1 oracle oinstall   98304 Feb 26 20:28 B_TAB_P002.dmp
-rw-r-----. 1 oracle oinstall  188416 Feb 26 20:28 B_TAB_P003.dmp
-rw-r-----. 1 oracle oinstall 1069056 Feb 26 20:28 B_TAB_P004.dmp

Review job status:

$ grep "successfully completed" exp*.log
exp_api.log:PL/SQL procedure successfully completed.
exp_A_TAB_P001.log:Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 26 20:28:09 2014 elapsed 0 00:00:01
exp_A_TAB_P002.log:Job "SYS"."SYS_EXPORT_TABLE_03" successfully completed at Wed Feb 26 20:28:10 2014 elapsed 0 00:00:02
exp_A_TAB_P003.log:Job "SYS"."SYS_EXPORT_TABLE_04" successfully completed at Wed Feb 26 20:28:11 2014 elapsed 0 00:00:02
exp_A_TAB_P004.log:Job "SYS"."SYS_EXPORT_TABLE_05" successfully completed at Wed Feb 26 20:28:13 2014 elapsed 0 00:00:02
exp_B_TAB_P001.log:Job "SYS"."SYS_EXPORT_TABLE_06" successfully completed at Wed Feb 26 20:28:14 2014 elapsed 0 00:00:02
exp_B_TAB_P002.log:Job "SYS"."SYS_EXPORT_TABLE_07" successfully completed at Wed Feb 26 20:28:16 2014 elapsed 0 00:00:02
exp_B_TAB_P003.log:Job "SYS"."SYS_EXPORT_TABLE_08" successfully completed at Wed Feb 26 20:28:17 2014 elapsed 0 00:00:03
exp_B_TAB_P004.log:Job "SYS"."SYS_EXPORT_TABLE_09" successfully completed at Wed Feb 26 20:28:19 2014 elapsed 0 00:00:02

Review exported partition:

$ grep "exported" exp*.log
exp_A_TAB_P001.log:. . exported "MDINH"."A_TAB":"P001"                      6.351 KB       9 rows
exp_A_TAB_P002.log:. . exported "MDINH"."A_TAB":"P002"                      14.89 KB      90 rows
exp_A_TAB_P003.log:. . exported "MDINH"."A_TAB":"P003"                      101.1 KB     900 rows
exp_A_TAB_P004.log:. . exported "MDINH"."A_TAB":"P004"                      963.3 KB    9000 rows
exp_B_TAB_P001.log:. . exported "MDINH"."B_TAB":"P001"                      6.351 KB       9 rows
exp_B_TAB_P002.log:. . exported "MDINH"."B_TAB":"P002"                      14.89 KB      90 rows
exp_B_TAB_P003.log:. . exported "MDINH"."B_TAB":"P003"                      101.1 KB     900 rows
exp_B_TAB_P004.log:. . exported "MDINH"."B_TAB":"P004"                      963.3 KB    9000 rows

Example of completed log:

$ cat exp_B_TAB_P001.log
Starting "SYS"."SYS_EXPORT_TABLE_06":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
. . exported "MDINH"."B_TAB":"P001"                      6.351 KB       9 rows
Master table "SYS"."SYS_EXPORT_TABLE_06" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_06 is:
  /tmp/B_TAB_P001.dmp
Job "SYS"."SYS_EXPORT_TABLE_06" successfully completed at Wed Feb 26 20:28:14 2014 elapsed 0 00:00:02

SQL Scripts:

exp_api.sql:

set timing on echo on
-- DataPump Export (EXPDP) Fails With Errors ORA-39001 ORA-39000 ORA-31641 ORA-27054 ORA-27037 When The Dump File Is On NFS Mount Point (Doc ID 1518979.1)
-- Work around for the above mentioned error
alter system set events '10298 trace name context forever, level 32';
declare
    h1 number;
    dir_name varchar2(30);
begin
    dir_name := 'DPDIR';
    for x in (
        select table_owner, table_name, partition_name
        from   dba_tab_partitions
        where  table_owner = 'MDINH' and table_name in ('A_TAB','B_TAB') and regexp_like(partition_name,'[0-4]$')
        order  by table_owner, table_name, partition_position
    ) loop

        h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE');

        dbms_datapump.add_file (
            handle    => h1,
            filename  => x.table_name||'_'||x.partition_name||'.dmp',
            reusefile => 1, -- REUSE_DUMPFILES=Y
            directory => dir_name,
            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

        dbms_datapump.add_file (
            handle    => h1,
            filename  => 'exp_'||x.table_name||'_'||x.partition_name||'.log',
            directory => dir_name,
            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

        -- CONTENT = DATA_ONLY    
        dbms_datapump.set_parameter (
            handle => h1,
            name   => 'INCLUDE_METADATA',
            value  => 0);

        dbms_datapump.metadata_filter (
            handle => h1,
            name   => 'SCHEMA_EXPR',
            value  => 'IN ('''||x.table_owner||''')');

        dbms_datapump.metadata_filter (
            handle => h1,
            name   => 'NAME_EXPR',
            value  => 'IN ('''||x.table_name||''')');

        dbms_datapump.data_filter (
            handle      => h1,
            name        => 'PARTITION_LIST',
            value       => x.partition_name,
            table_name  => x.table_name,
            schema_name => x.table_owner);

        dbms_datapump.start_job (handle => h1);
        dbms_datapump.detach (handle => h1);
    end loop;
end;
/
alter system set events '10298 trace name context off';
exit

Reference:

DBMS_DATAPUMP