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.
