개요

본 문서에서는 전환이 완료된 후 DB 측에서 확인하는 사항인 Object 개수와 데이터 검증 방법에 대해 설명합니다.

방법

1. Object 개수

1) TABLE

set linesize 150
column owner format a30

select
       owner,
       count(table_name) as table_cnt
from
       dba_tables
where
       owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

2) VIEW

set linesize 150
column owner format a30

select
       owner,
       count(view_name) as view_cnt
from
       dba_views
where
       owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

3) SYNONYM

set linesize 150
column owner format a30

select
       owner,
       count(1) as "synonym_cnt"
from
       dba_synonyms
where
       owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

4) SEQUENCE

set linesize 150
column sequence_owner format a30

select
       sequence_owner,
       count( sequence_name ) as "sequence_cnt"
from
       dba_sequences
where
       sequence_owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by sequence_owner
order by sequence_owner;

5) PACKAGE

set linesize 150
column owner format a30

select
       owner,
       count( name ) as "package_cnt"
from
       dba_source
where
       type = 'PACKAGE' and owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

6) PACKAGE BODY

set linesize 150
column owner format a30

select
       owner,
       count( name ) as "package_body_cnt"
from
       dba_source
where
       type = 'PACKAGE BODY' and owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

7) FUNCTION

set linesize 150
column owner format a30

select
       owner,
       count( name ) as "function_cnt"
from
       dba_source
where
       type = 'FUNCTION' and owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

8) PROCEDURE

set linesize 150
column owner format a30

select
       owner,
       count( name ) as "procuedure_cnt"
from
       dba_source
where
       type = 'PROCEDURE' and owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

9) INDEX

set linesize 150
column owner format a30

select
       owner,
       count( index_name ) as "index_cnt"
from
       dba_indexes
where
       owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

10) CONSTRAINT

set linesize 150
column owner format a30

select
       owner,
       constraint_type,
       count( constraint_name ) as "constraint_cnt"
from
       dba_constraints
where
       owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner, constraint_type
order by owner ,constraint_type;

11) GRANT

set linesize 150
column col format a30

select
       owner,
       count(1) as "grant_cnt"
from
       dba_tab_privs
where
       owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

12) TRIGGER

set linesize 150
column owner format a30

select
       owner,
       count(trigger_name) as "trigger_cnt"
from
       dba_triggers
where
       owner not in ('SYS','SYSGIS','SYSCAT','PUBLIC','OUTLN','TIBERO','TIBERO1')
group by owner
order by owner;

2. 데이터 검증

DB LINK 및 minus 쿼리 사용하여 row count 를 검증합니다.

1) count 건수 비교

select 'tbl1' as table_name,
       count(*) as oracle_count, 
       count(*) - (select
 count(*) from tbl1 where rownum = 1) as diff from tbl1@ora;

2) Minus 결과 비교

select * from tbl1@ora minus
select * from tbl1;