Overview

This document explains how to verify the number of objects and data validation methods on the DB side after the conversion is completed.

Method

1. Number of Objects

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. Data Validation

Verify row count using DB LINK and minus queries.

1) Compare count values

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

2) Compare minus results

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