개요

이관 후 검증을 위해 function 사용하는 방법에 대한 방법을 안내합니다. 

방법

1.tbdsn.tbr에 ASIS(구DB), TOBE(신DB) 내용 추가

ASIS=(
(INSTANCE=(HOST=192.168.41.110)
(PORT=8629)
(DB_NAME=tibero)
)
)

TOBE=(
(INSTANCE=(HOST=localhost)
 (PORT=8629)
 (DB_NAME=tibero)
)
 )

2. DB LINK 생성

create public database link asis connect to sys identified by tibero using 'ASIS';

create public database link tobe connect to sys identified by tibero using 'TOBE';

3. LINK 접속 테스트

SQL> select * from dual@asis;

DUMMY
-----
X

SQL> select * from dual@tobe;

DUMMY
-----
X

4. USER 생성

create user TB_MIG identified by TB_MIG;

grant dba to TB_MIG;

conn TB_MIG/TB_MIG

5. TYPE 생성

CREATE OR REPLACE TYPE MIG_VERIFY_STRUCT AS OBJECT (

ASIS_OWNER VARCHAR2(4000), -- ASIS OWNER

ASIS_OBJECT_NAME VARCHAR2(4000), -- ASIS OBJECT

ASIS_OBJECT_STATUS VARCHAR2(4000), -- ASIS OBJECT STATUS

TOBE_OWNER VARCHAR2(4000), -- TOBE OWNER

TOBE_OBJECT_NAME VARCHAR2(4000), -- TOBE OBJECT

TOBE_OBJECT_STATUS VARCHAR2(4000), -- TOBE OBJECT STATUS

OBJECT_VERIFY VARCHAR2(4000), -- OBJECT VERIFY

STATUS_VERIFY VARCHAR2(4000) -- STATUS VERIFY

)

/

CREATE OR REPLACE TYPE MIG_VERIFY_TYPE IS TABLE OF MIG_VERIFY_STRUCT

/

6. FUNCTION 생성

CREATE OR REPLACE FUNCTION FN_MIG_VERIFY

(

IN_OWNER VARCHAR2,

IN_OBJECT_TYPE VARCHAR2

)

RETURN MIG_VERIFY_TYPE PIPELINED

IS

 V_TYPE MIG_VERIFY_STRUCT := MIG_VERIFY_STRUCT ('','','','','','','','');



BEGIN



FOR REC IN (

SELECT ASIS_OWNER,

ASIS_OBJNAME,

ASIS_OBJSTATUS,

TOBE_OWNER,

TOBE_OBJNAME,

TOBE_OBJSTATUS,

OBJ_VERIFY,

STATUS_VERIFY

FROM

(

SELECT ASIS.OWNER ASIS_OWNER,

ASIS.OBJECT_NAME ASIS_OBJNAME,

ASIS.STATUS ASIS_OBJSTATUS,

TOBE.OWNER TOBE_OWNER,

TOBE.OBJECT_NAME TOBE_OBJNAME,

TOBE.STATUS TOBE_OBJSTATUS,

CASE WHEN (ASIS.OWNER = TOBE.OWNER AND ASIS.OBJECT_NAME = TOBE.OBJECT_NAME) THEN 'O' ELSE 'X'

END AS OBJ_VERIFY,

CASE WHEN ( ASIS.OBJECT_NAME = TOBE.OBJECT_NAME AND ASIS.STATUS=TOBE.STATUS) THEN 'O' ELSE 'X'

END AS STATUS_VERIFY FROM

(

(

SELECT OWNER,

OBJECT_NAME,

STATUS

FROM DBA_OBJECTS@ASIS

WHERE OWNER = IN_OWNER

AND OBJECT_TYPE = IN_OBJECT_TYPE

) ASIS

FULL OUTER JOIN

(

SELECT OWNER,

OBJECT_NAME,

STATUS FROM DBA_OBJECTS@TOBE

WHERE OWNER = IN_OWNER

AND OBJECT_TYPE = IN_OBJECT_TYPE

) TOBE

ON ( ASIS.OWNER = TOBE.OWNER AND ASIS.OBJECT_NAME = TOBE.OBJECT_NAME )

)

)

ORDER BY 1,4,2,5

)

LOOP



V_TYPE.ASIS_OWNER := REC.ASIS_OWNER;

V_TYPE.ASIS_OBJECT_NAME := REC.ASIS_OBJNAME;

V_TYPE.ASIS_OBJECT_STATUS := REC.ASIS_OBJSTATUS;

V_TYPE.TOBE_OWNER := REC.TOBE_OWNER;

V_TYPE.TOBE_OBJECT_NAME := REC.TOBE_OBJNAME;

V_TYPE.TOBE_OBJECT_STATUS := REC.TOBE_OBJSTATUS;

V_TYPE.OBJECT_VERIFY := REC.OBJ_VERIFY;

V_TYPE.STATUS_VERIFY := REC.STATUS_VERIFY;

PIPE ROW (V_TYPE);

END LOOP;

END;

/

조회 방법

SET LINES 6000

SET PAGES 20000

COL ASIS_OWNER FOR A30

COL ASIS_OBJECT_NAME FOR A50

COL ASIS_OBJECT_STATUS FOR A10

COL TOBE_OWNER FOR A30

COL TOBE_OBJECT_NAME FOR A50

COL TOBE_OBJECT_STATUS FOR A10

COL OBJECT_VERIFY FOR A10

COL STATUS_VERIFY FOR A10

select * from table(FN_MIG_VERIFY(,FN_MIG_VERIFY (<OWNER>.<OBJECT_TYPE>));

조회 쿼리 예시

TEST계정의 TABLE 검증

select * from table(FN_MIG_VERIFY('TEST','TABLE')) ;

TEST 계정의 INDEX 검증

select * from table(FN_MIG_VERIFY('TEST','INDEX')) ;