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.
No comments:
Post a Comment