diffobj.sql
/*************************************************************************/ /* objdiff.sql - Lists the objects in a schema that are not in both of */ /* two instances. Uses database links and the SQL MINUS */ /* operator to make the comparison. */ /* */ /* Author: Ken Atkins (katkins@cco.net) */ /* http://www.cco.net/~katkins/oratip */ /* */ /* Written: 5/11/95 */ /* */ /* You need to have a database link setup for any instance that you want */ /* to make a comparison for. */ /* */ /* Please feel free to use and modify this script as long it is not sold */ /* or included in any software without the prior permission of the author*/ /* If you do make some good improvements, please send them to me, and I */ /* can incorporate them in a future version and make them available to */ /* others (giving you credit of course!). */ /* */ /*************************************************************************/ set pagesize 60 set linesize 80 set verify off set feedback off set pause off; --define obj_owner = '&1' --define inst_1 = '&2' --define inst_2 = '&3' accept obj_owner prompt 'Object Owner: ' accept inst_1 prompt 'First instance DB Link (Include @):' accept inst_2 prompt 'Second instance DB Link (Include @):' clear breaks ttitle off set heading off column datetime noprint new_value datetime column inst_code1 noprint new_value inst_code1 column inst_code2 noprint new_value inst_code2 select to_char(sysdate,'MM/DD/YY') datetime from dual / select value inst_code1 from v$parameter&inst_1 where name = 'db_name' / select value inst_code2 from v$parameter&inst_2 where name = 'db_name' / set feedback on set heading on set newpage 0 ttitle left 'OBJDIFF'- col 25 'OBJECT DIFFERENCE REPORT' - col 53 'Report Date: ' datetime - skip 1 col 60 'Page: ' sql.pno - skip 1 col 10 'OWNER: ' obj_owner - skip 1 center 'Objects in &inst_code1 but not &inst_code2' - skip 2 set null=0 column object_type format a15 heading 'Object Type'; column object_name format a35 heading 'Object Name'; column status format a10 heading 'Status'; column inst_code format a10 heading 'Instance'; select object_name, object_type, status from all_objects&inst_1 where owner = UPPER('&obj_owner') -- and object_type != 'SYNONYM' MINUS select object_name, object_type, status from all_objects&inst_2 where owner = UPPER('&obj_owner') -- and object_type != 'SYNONYM' order by 2,3 / set heading off; set feedback off; select '' from dual / set heading on; set feedback on; ttitle left 'OBJDIFF'- col 25 'OBJECT DIFFERENCE REPORT' - col 53 'Report Date: ' datetime - skip 1 col 60 'Page: ' sql.pno - skip 1 col 10 'OWNER: ' obj_owner - skip 1 center 'Objects in &inst_code2 but not &inst_code1' - skip 2 select object_name, object_type, status from all_objects&inst_2 where owner = UPPER('&obj_owner') and object_type != 'SYNONYM' MINUS select object_name, object_type, status from all_objects&inst_1 where owner = UPPER('&obj_owner') and object_type != 'SYNONYM' order by 2,3 /