SQL, perl und Unix/Linux Schulungen in und um Wien

Compare available Objects on two Oracle Instances

For this snippet to work you need a functioning database link


select object_name, object_type from user_objects@;
minus
select object_name, object_type from user_objects


to get the actual DDL (for a package body in this example) statments you have to issue


select referenced_name, dbms_metadata.get_ddl(referenced_type, referenced_name) from (
select name, type, referenced_name, referenced_type from
user_dependencies where name='' and type='PACKAGE BODY'
minus
select name, type, referenced_name, referenced_type from user_dependencies@;
) where referenced_name not in ('STANDARD', 'DBMS_OUTPUT', 'DUAL')
and referenced_name not in (select object_name from user_objects@)