[오라클] DAILY 테이블 이관 프로시저 스크립트 (DB간 싱크, DDL 활용)

DAILY 테이블 이관 프로시저

개발 DB <-> 전환 DB 간 테이블 싱크 맞추는 작업을 자동화하기 위한 스크립트 ( DDL 활용 )

 

1. 스케줄러 실행 프로시저

 

2. 실제 IMPDP 수행 프로시저

 CREATE OR REPLACE PROCEDURE DBADM.PRC_IMPDP(
 	P_OWNER   IN VARCHAR DEFAULT NULL
   ,P_TABLE   IN VARCHAR DEFAULT NULL
   ,P_LOG_YN  IN VARCHAR DEFAULT 'N'
)
IS

-- 1.1 DB 링크를 이용하여 복사하기 
-- DECLARE
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 DBADM.TB_DDL_HISTORY@DL_DIGWDB_DBADM
             WHERE EXEC_DTM BETWEEN TO_DATE
               AND USERNAME IN ('DBADM','SYS','SYSTEM')
               AND OBJECT_OWNER IN ('IGWOWN','MBMS')
               AND OBJECT_TYPE IN ('TABLE','COLUMN')
               AND ACTION_EVENT NOT IN ('DROP','COMMENT','TRUNCATE','ANALYZE','PURGE')
               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_mode => 'SCHEMA'
                           ,job_name => v_job_name
                           ,remote_link => 'DL_DIGWDB_DBADM'); -- dblink 명
                           
	dbms_output.put_line(v_job_name);
    dbms_output.put_line(dph);
    
    --로그
    IF P_LOG_YN = 'Y' THEN
    	dbms_datapump.add_file(handle => dph,
        					   filename => v_job_name || '.log',
                               directory => 'DATA_PUMP_DIR' , 
                               filetype = KU$_FILE_TYPE_LOG_FILE);
    END IF;
    
    -- 스키마 정보
    dbms_datapump.metadata_filter(handle => dph,
    							  name   => 'SCHEMA_EXPR',
                                  value  => ' IN ('''||P_OWNER||''')');
    
    -- 테이블 존재 시
    dbms_datapump.set_parameter(handle => dph,
                                name   => 'TABLE_EXISTS_ACTION',
                                value  => 'REPLACE');
    --테이블 정보
    dbms_datapump.metadata_filter(handle  => dph ,
                                  name    => 'NAME_EXPR',
                                  value   => 'REPLACE');
    
    -- 테이블 존재시