Handy Query to Compare Objects in Multiple Oracle Databases

As a DBA, more than often than not, you need to compare database objects in different databases. There are few tools available, but following query gives you a quick bird view of what objects are present and how many. You can filter them schema by schema.

 

Now these objects could be packages, indexes, sequences, functions ,procedures, synonyms, materialized views, jobs and others. 

SQL> col SCHEMA for a8

SQL> select owner SCHEMA,sum(decode(object_type,'CLUSTER',1,0)) CLSTR,sum(decode(object_type,'TABLE',1,0)) "TABLE",sum(decode(object_type,'INDEX',1,0)) "INDEX",

  2  sum(decode(object_type,'SEQUENCE',1,0)) "SEQNC",sum(decode(object_type,'TRIGGER',1,0)) "TRIGR",sum(decode(object_type,'FUNCTION',1,0)) "FUNCT",

  3  sum(decode(object_type,'PROCEDURE',1,0)) "PROCD",sum(decode(object_type,'PACKAGE',1,0)) "PACKG",sum(decode(object_type,'PACKAGE BODY',1,0)) "PCKBD",

  4  sum(decode(object_type,'VIEW',1,0)) "VIEWS",sum(decode(object_type,'SYNONYM',1,0)) "SYNYM",sum(decode(object_type,'MATERIALIZED VIEW',1,0)) "MVIEW",

  5  sum(decode(object_type,'TYPE',1,0)) "TYPES",sum(decode(object_type,'JOB',1,0)) "JOBS"

  6  from dba_objects where owner in ('ADMIN','AVA','FOGLIGHT','TEST','TEST1') group by rollup(owner) order by owner;

SCHEMA       CLSTR TABLE     INDEX      SEQNC   TRIGR      FUNCT PROCD    PACKG      PCKBD VIEWS     SYNYM      MVIEW   TYPES       JOBS

-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

ADMIN   0     14 4    0       0 0     5        1   1      0 0    0       0 0

AVA   0     46        21    2       4 1     2        1   1      0 0    0       0 0

FOGLIGHT   0      7 2    0       0 0     0        2   2      1 0    0       0 0

TEST   0      3 2    4       0 1     3        6   0      0 0    0       0 0

TEST1   0     38        51    0       0 8     3        0   0      1 0   26       0 0

SUM   0    108        80    6       4 10    13       10   4      2 0   26       0 0

6 rows selected.

SQL> select owner, object_name, object_type, status from dba_objects where status!='VALID';

OWNER        OBJECT_NAME       OBJECT_TYPE   STATUS

------------------------------ ------------------------------ ------------------- -------

TEST1        MBANNERVIEW       VIEW   INVALID

TEST        TRUNCATE_SHOWICON       PROCEDURE   INVALID

PUBLIC        MERCHANT_NETWORK       SYNONYM   INVALID

PUBLIC        CURRENCY_NETWORK       SYNONYM   INVALID