Dbms datapump 데이터 필터링

1 [DBMS_DATAPUMP].METADATA_FILTER 함수 - TABLE DDL METADATA_ONLY 처리[편집]

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;

2 [DBMS_DATAPUMP api] TABLE DDL 반영 - METADATA_ONLY 처리[편집]

테스트용 테이블 생성

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;

[datapump api] dbms_datapump.metadata_filter함수 SCHEMA_LIST , NAME_LIST 이용

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 => '''DBCAFE''');
   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;
/

SUBQUERY 사용

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');

Dbms datapump 데이터 필터링

DB CAFE

1 [DBMS_DATAPUMP].METADATA_FILTER 함수 - TABLE DDL METADATA_ONLY 처리[편집]

2 [DBMS_DATAPUMP api] TABLE DDL 반영 - METADATA_ONLY 처리[편집]