AS-IS는 Oracle 11g , TO-BE는 12c 로 테스트를 진행 했으며
Export / Import로 데이터를 이전 할 경우 TO-BE에서 AS-IS로 DB Link를 생성 한 후에 TO-BE에서 체크 쿼리를 수행 함.
-- object check
with
asis as
(select object_type , count(*) total_cnt ,
sum(case when status ='VALID' then 1 else 0 end) as valid_cnt ,
sum(case when status <> 'VALID' then 1 else 0 end) as invalid_cnt
from user_objects@ora11g
group by object_type
order by 1 ) ,
tobe as
(select object_type , count(*) total_cnt ,
sum(case when status ='VALID' then 1 else 0 end) as valid_cnt ,
sum(case when status <> 'VALID' then 1 else 0 end) as invalid_cnt
from user_objects
group by object_type
order by 1 )
SELECT asis.object_type ,
asis.total_cnt asis_total_cnt , tobe.total_cnt tobe_total_cnt ,
asis.valid_cnt asis_valid_cnt , tobe.valid_cnt tobe_valid_cnt ,
asis.invalid_cnt asis_invalid_cnt , tobe.invalid_cnt tobe_invalid_cnt
FROM tobe, asis
WHERE asis.object_type = tobe.object_type
AND ( asis.total_cnt <> tobe.total_cnt
OR
asis.valid_cnt <> tobe.valid_cnt
OR
asis.invalid_cnt <> tobe.invalid_cnt) ;
-- row count check
with
asis as
( select table_name,
to_number( extractvalue( xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name||'@ora11g') ) ,'/ROWSET/ROW/C')) cnt
from user_tables@ora11g
order by table_name ) ,
tobe as
( select table_name,
to_number( extractvalue( xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name) ) ,'/ROWSET/ROW/C')) cnt
from user_tables
order by table_name )
SELECT asis.table_name , asis.cnt as asis_cnt , tobe.cnt as tobe_cnt
FROM tobe , asis
WHERE asis.table_name = tobe.table_name
AND asis.cnt <> tobe.cnt ;