[오라클] DB내 스키마 테이블 이관 스크립트 ft. DDL 추출 프로시저

오라클 DB내 스키마 테이블 이관

앞전에 개발DB, 전환DB SYNC 맞추는 작업 (DBLINK로 DATAPUMP) 은 스케줄잡 등록해서 매일 동기화 시켜주고 있다.

전환 개발자의 새로운 요청

전환 DB에서 'IGWOWN','MBMS' 계정의 테이블을 'IGWMIG','MBMSMIG' 계정에 똑같이 만들어주세요

이번에도 IMPDP를 해야하나..? 생각했는데 생각해보니 같은 DB자나? 

 

처음 생각한거 => CTAS 활용 

하지만 아니었죠. CREATE TABLE AS SELECT 로 테이블 만들면 PK 인덱스 안가져올거니?  CONSTRAINTS 는? ㅠㅠ

 

근데 생각해보니

그냥 DDL 뽑아서 프로시저 만들어서 돌리면 되는거ㅈ ㅑ나 ~?

프로시저 연습할겸 작성 시작.

CREATE OR REPLACE PROCEDURE DBADM.PRC_MIG_SCH (
	P_ASIS_OWNER IN VARCHAR DEFAULT NULL
   ,P_TOBE_OWNER IN VARCHAR DEFAULT NULL
) AUTHID CURRENT_USER

CURSOR c_tables IS
	/********추출 대상 테이블 수정 필요 *********/
    SELECT OBJECT_NAME AS TABLE_NAME, OWNER
      FROM DBA_OBJECTS
     WHERE OBJECT_TYPE = 'TABLE'
       AND OWNER = P_ASIS_OWNER;
    /********************************************/
    v_table_name varchar2(128);
    v_ddl varchar2(32767);
    s_ddl varchar2(32767);

BEGIN
	dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'BODY', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS', true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PARTITIONING', true);
    
    FOR r_table IN c_tables LOOP
    	BEGIN
        	v_table_name := r_table.table_name
            /* 텍스트가 길기 떄문에 버퍼 크기 크게 바꿔준다. */
            DBMS_OUTPUT.ENABLE(32767);
            DBMS_OUTPUT.PUT_LINE(r_table.table_name);
            /* 변수에 DDL 할당 */
            v_ddl := '/************[ START DDL for ' || r_table.table_name|| ' ] **************/';
            v_ddl := v_ddl || CHR(10) || '-- 1.TABLE (PK INDEX 및 CONSTRAINTS 포함)' || dbms_metadata.get_ddl('TABLE',r_table.table_name, r_table.owner);
            v_ddl := v_ddl || CHR(10) || '/***********[END DDL FOR ' || r_table.table_name || ' ] ************/' || CHR(10) || CHR(10);
            
            SELECT REGEXP_REPLACE(v_ddl,'TS_[A-Z]{2}_','TS_DEVMIG_') INTO s_ddl FROM DUAL;
            SELECT REGEXP_REPLACE(s_ddl,'TS_MBMS_','TS_DEVMIG_') INTO s_ddl FROM DUAL;
            SELECT REGEXP_REPLACE(s_ddl,'P_ASIS_OWNER,P_TOBE_OWNER) INTO s_ddl from dual;
            SELECT REGEXP_REPLACE(s_ddl,';','') INTO s_ddl from dual;
            
            /*** 실행문 출력 후 실행 ***/
            DBMS_OUTPUT.PUT_LINE(s_ddl);
            EXCUTE IMMEDIATE s_ddl;
        EXCEPTION
        	WHEN OTHERS THEN
            	DBSM_OUTPUT.PUT_LINE('ERROR IN LOOP : ' || SQLERRM );
        END;
        
        DBMS_OUTPUT.PUT_LINE('--TABLE_COUNT : '||c_tables%rowcount);
        /*** 로그 테이블에 삽입 ***/
        INSERT INTO DBADM.SCH_MIG_LOG (ASIS_OWNER, TOBE_OWNER, TABLE_NAME, EXE_TIME)
        					   VALUES (P_ASIS_OWNER,P_TOBE_OWNER,V_TABLE_NAME,SYSDATE);
        COMMIT;
    END LOOP;
END;
/

 

잘 돌아가는거 매우몹시잘확인완료.

문제는 이거 스케줄 잡에 등록하려하는데 주기 얼마로 잡을지 아직 전환팀이랑 소통하지 모타여따.

끗!