Dbms datapump 데이터 필터링
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
PROCEDURE export_data(p_directory IN VARCHAR2) IS
CURSOR c_norows IS
select owner, table_name from dba_tables WHERE owner in (
select distinct owner from reload_dev_tables) and status='VALID' and temporary = 'N'
and secondary = 'N' and nested = 'NO' and dropped = 'NO' and iot_name is null
minus
select owner, table_name from reload_dev_tables;
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
s varchar2(3000);
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'dev_dw.dmp',
directory => p_directory);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'dev_dw.log',
directory => p_directory,
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
--
SELECT listagg(''''||owner||'''', ', ') WITHIN GROUP (ORDER BY owner) INTO s
FROM (SELECT DISTINCT owner FROM dba_tables WHERE owner IN (SELECT distinct owner from reload_dev_tables));
DBMS_DATAPUMP.metadata_filter(l_dp_handle, 'SCHEMA_LIST', s);
-- Add query filters
FOR rec IN (SELECT owner, table_name, filter_predicate FROM reload_dev_tables r
WHERE filter_predicate IS NOT NULL AND
EXISTS (SELECT 1 FROM dba_tables t WHERE t.owner = r.owner AND t.table_name = r.table_name
AND t.dropped = 'NO') ) LOOP
DBMS_DATAPUMP.DATA_FILTER (
handle => l_dp_handle,
name => 'SUBQUERY',
value => 'WHERE '||rec.filter_predicate,
table_name => rec.table_name,
schema_name => rec.owner);
END LOOP;
-- Add tables without rows
FOR rec IN c_norows LOOP
DBMS_DATAPUMP.DATA_FILTER (
handle => l_dp_handle,
name => 'INCLUDE_ROWS',
value => 0,
table_name => rec.table_name,
schema_name => rec.owner);
END LOOP;
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END;
PLSQL: Using Datapump API with filter on multiple tables
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.