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:
Oracle Database Consulting Services
Ready to optimize your Oracle Database for the future?
.svg){kind=small}
.svg){kind=small}