jcon.no: Oracle blog

The developers in one of the teams I work within wanted to export the tables in a schema with limited number of rows. This could of course be done by running  expdp command line.  But this time we needed to find the last x numbers of primary keys in one of the central tables and export only these rows. This opted for the use of the DBMS_DATAPUMP API. It took me some minutes to figure out how to put on a filter on multiple tables, but it was not really that hard. Here is what i did.

I created three test tables:

SQL> connect lj
Enter password:
Connected.

SQL> create table t1 (id number, tekst varchar2(10));
SQL> create table t2 (id number, tekst varchar2(10));
SQL> create table t3 (id number, tekst varchar2(10));
SQL> insert into t1 values (1, 'tekst 1');
SQL> insert into t1 values (2, 'tekst 2');
SQL> insert into t2 values (1, 'tekst 1');
SQL> insert into t2 values (2, 'tekst 2');
SQL> insert into t3 values (1, 'tekst 1');
SQL> insert into t3 values (2, 'tekst 2');
SQL> commit;

Then I want to write a PLSQL which filters ID=1 from table T1, and ID=2 from table T2,and with no filter on table T3. First I wrote the PL/SQL to export all full tables:

SQL> connect system
Enter password:
Connected.
SQL> 
declare
   h1 NUMBER;
   l_status varchar2(2000);
begin
   h1 := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', job_name => 'TBL_EXP_TST');
   dbms_datapump.add_file(handle => h1, filename => 't_tst.dmp', directory=> 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
   dbms_datapump.add_file(handle => h1, filename => 't_tst.log', directory=> 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
   dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => '''LJ''');
   dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST', value => '''T1'',''T2'',''T3''');
   dbms_datapump.start_job(handle => h1);
   dbms_datapump.wait_for_job( handle => h1, job_state => l_status );
   dbms_datapump.detach(handle => h1);
exception when others then
   dbms_datapump.detach(handle => h1);
   raise;
end;
/

This works perfectly fine and the following output is written to the t_tst.log file (Sorry for the Norwegian output, but you can probably find what you need):

Starter "SYSTEM"."TBL_EXP_TST":
Beregning pågår ved hjelp av metoden BLOCKS...
Behandler objekttypen TABLE_EXPORT/TABLE/TABLE_DATA
Total beregning ved hjelp av metoden BLOCKS: 128 KB
Behandler objekttypen TABLE_EXPORT/TABLE/TABLE
. . eksporterte "LJ"."T1"                                   5.414 KB       2 rader
. . eksporterte "LJ"."T2"                                   5.414 KB       2 rader
. . eksporterte "LJ"."T3"                                   5.414 KB       2 rader
Hovedtabellen "SYSTEM"."TBL_EXP_TST" ble lastet inn/ut
******************************************************************************
Dumpefilsettet for SYSTEM.TBL_EXP_TST er:
  /opt/oracle/admin/EREXO/dpdump/t_tst.dmp
Jobben "SYSTEM"."TBL_EXP_TST" ble fullført, 12:52:31

So now it was time to put on some filters. I can put on a general filter like this:

dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => '''LJ''');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST', value => '''T1'',''T2'',''T3''');
dbms_datapump.data_filter(handle => h1, name => 'SUBQUERY', value => 'WHERE id = 1');

But this will filter id=1 on all three tables. Reading the manual (which you really should do once in a while) I found that the DBMS_DATAPUMP.DATA_FILTER procedure has both an TABLE_NAME and SCHEMA_NAME in-parameter. So I tried this on the T1 and T2 table:

dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => '''LJ''');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST', value => '''T1'',''T2'',''T3''');
dbms_datapump.data_filter(handle => h1, table_name=>'T1', schema_name=>'LJ', name => 'SUBQUERY', value => 'WHERE id = 1');
dbms_datapump.data_filter(handle => h1, table_name=>'T2', schema_name=>'LJ', name => 'SUBQUERY', value => 'WHERE id = 2');

And this seemed to work quite all right. My output to the t_tst.log file was now:

Behandler objekttypen TABLE_EXPORT/TABLE/TABLE
. . eksporterte "LJ"."T1"                                   5.398 KB       1 rader
. . eksporterte "LJ"."T2"                                   5.398 KB       1 rader
. . eksporterte "LJ"."T3"                                   5.414 KB       2 rader
Hovedtabellen "SYSTEM"."TBL_EXP_TST" ble lastet inn/ut

So the task was pretty much straight forward. Then I’m ready to write some code for my developers.