반응형

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 ;

반응형

+ Recent posts