Scripts que uso para manejo de objetos en Oracle

Estos scripts los uso frecuentemente para buscar objetos entre los schemas

 -------------- BUSCAR OBJETOS

select referenced_owner || '.' || referenced_name as table_name,

       referenced_type as type,

       owner || '.' || name as referencing_object,

       type as referencing_type

from sys.dba_dependencies

where referenced_type in('TABLE', 'VIEW')

      and referenced_name = 'DET_BULTOS_ENTREGA' -- put your table/view name here

      --and referenced_owner = 'schema name'

      and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',

          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',

          'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',

          'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 

          'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',

          'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',

          'OUTLN', 'WKSYS',  'LBACSYS') 

order by referencing_object;

-----------------BUSCAR SESIONES USANDO OBJETO

SELECT S.SID, s.serial#, username U_NAME, owner OBJ_OWNER,

object_name, object_type, s.osuser,

DECODE(l.block,

0, 'Not Blocking',

1, 'Blocking',

2, 'Global') STATUS,

DECODE(v.locked_mode,

0, 'None',

1, 'Null',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share',

5, 'S/Row-X (SSX)',

6, 'Exclusive', TO_CHAR(lmode)

) MODE_HELD

FROM gv$locked_object v, dba_objects d,

gv$lock l, gv$session s

WHERE v.object_id = d.object_id

AND (v.object_id = l.id1)

AND v.session_id = s.sid

ORDER BY username, session_id;

COLUMN owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN edition_name FORMAT A15

-------------------BUSCAR MODIFICACION OBJETOS

SELECT owner,

       object_name,

       --subobject_name,

       object_id,

       data_object_id,

       object_type,

       TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,

       TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') AS last_ddl_time,

       timestamp,

       status,

       temporary,

       generated,

       secondary,

       --namespace,

       edition_name

FROM   dba_objects

WHERE  UPPER(object_name) LIKE UPPER('DET_INDEX1')

ORDER BY owner, object_name;