Dbms datapump 데이터 필터링

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;
Dbms datapump 데이터 필터링

DB CAFE
