How to compare the number of objects per schema in two databases

After a migration of a database to a new server, or after any kind of replication, you would like to get some verification that your objects were created.

A potentially very useful query could help you with this task.

You need a database link from the target database back to the source database

create public database link proddb01_old 
connect to system
identified by mysecretpassword
using 'myproddb01';

Verify that the link works as intended:

SQL> select d.name,i.host_name from v$database d, v$instance i;

NAME      HOST_NAME
--------- -------------------------------
PRODDB01 myserver01.mydomain.com

Save the following in a script, and execute it from the target database via sqlplus:

set lines 200
col count_new format 99999
col count_old format 99999
col "object owner" format a30
col "Result" format a20
set pages 100

prompt ============================================
prompt compare users and their object count
prompt ============================================

WITH newprod_users AS(
 SELECT target.owner AS new_owner, count(*) AS count_new
 FROM   dba_objects target
 GROUP BY owner
 ORDER BY 1
),
oldprod_users AS (
 SELECT source.owner AS old_owner, count(*) AS count_old
 FROM   dba_objects@proddb01_old  source
 GROUP BY owner
 ORDER BY 1)
SELECT newprod_users.new_owner "object owner", 
       newprod_users.count_new "NEW PROD",  
       oldprod_users.count_old "OLD PROD",
       DECODE(newprod_users.count_new,
              oldprod_users.count_old,'Num rows identical','Num row differs') "Result"
FROM   newprod_users JOIN oldprod_users
ON     newprod_users.new_owner = oldprod_users.old_owner
ORDER BY "Result" DESC;

Example output:

============================================
compare number of users
============================================

object owner                     NEW PROD  OLD_PROD Result
------------------------------ ---------- ---------- --------------------

USER1                               1329       1329 Num rows identical
USER2                                  2          2 Num rows identical
USER3                               1750       1750 Num rows identical
USER4                                409        389 Num row differs
USER5                                961       1167 Num row differs
USER6                                251        256 Num row differs

The report can be easily modified to produce for example a semi-colon separated list, which can in turn be pulled into an MS Excel file if desirable.