[PL/SQL] DBLINK 사용 IMPDP 테이블 이관 프로시저

오라클 DBLINK 사용 IMPDP 테이블 이관 프로시저

CREATE OR REPLACE PROCEDURE PRC_IMPDP(
    P_OWNER IN VARCHAR DEFAULT NULL
   ,P_TABLE IN VARCHAR DEFAULT NULL)
   
IS
--1.1 DB 링크를 이용하여 복사
dph NUMBER;
v_job_name VARCHAR(100) := 'IMPDP_DIGWDB_'||TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS');
v_mdy_tab_list varchar(32767)
BEGIN
IF P_TABLE IS NULL THEN
    /* 수정된 테이블 조회 */
    SELECT ''''||tab_list||'''' INTO v_mdy_tab_list
      FROM (
          SELECT LISTAGG(tABLE_NAME,''',''') WITHIN GROUP(ORDER BY TABLE_NAME) tab_list
          FROM (
              SELECT OBJECT_OWNER||','||OBJECT_NAME AS TABLE_NAME
              FROM TB_DDL_HISTORY@DL_DIGWDB_DBADM
             WHERE EXEC_DTM BETWWNE TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD')||' 000000','YYYYMMDD HH24MISS') AND SYSDATE 
               AND OBJECT_OWNER IN ('IGWOWN', 'MBMS')
               AND REGEXP_LIKE(OBJECT_NAME, '[^0-9]$')
             GROUP BY OBJECT_OWNER, OBJECT_NAME
             )
          );
ELSE
    SELECT ''''||P_TABLE||'''' into v_mdy_tab_list
      FROM DUAL;
END IF;
      
/*DB_LINK 이용 */
     
    dph := DBMS_DATAPUMP.OPEN( operation => 'IMPORT'
                             , job_mod => 'SCHEMA'
                             , job_name => v_job_name
                             , remote_link => 'DL_DIGWDB_DBADM');
/*로그*/
    
    dbms_datapump.add_file(handle => dph,
                           filename => v_job_name ||'.log',
                           directory => 'DATA_PUMP_DIR', filetype=>3);
-- 스키마정보
    dbms_datapump.METADATA_FILTER(handle => dph,
                           name => 'SCHEMA_EXPR',
                           value => ' IN ('''||P_OWNER||''')');
      
--테이블 존재시 TRUNCATE/REPLACE/APPEND
    
    dbms_datapump.set_parameter(handle => dph,
                           name => 'TABLE_EXISTS_ACTION',
                           value => 'REPLACE');
                           
--테이블 정보
    
    dbms_datapump.METADATA_FILTER(handle => dph,
                           name => 'INCLUDE_PATH_EXPR',
                           value => ' IN ('||v_mdy_tab_list||')',
                           object_type => 'TABLE');
--테이블 정보
    dbms_datapump.METADATA_FILTER(handle => dph,
                           name => 'INCLUDE_PATH_EXPR',
                           value => ' IN (''TABLE'')');
--테이블 정보
    
    dbms_datapump.METADATA_FILTER(handle => dph,
                           name => 'EXCLUDE_PATH_EXPR',
                           value => ' IN (''INDEX'',''GRANTS'',''STATISTICS'')');
                           
dbms_datapump.start_job(dph);
dbms_datapump.detach(dph);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error:' ||sqlerrm || ' on JOB-ID:' || dph);
END;
/