查询依赖对象(嵌套)



@E:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/utldtree.sql
--/s01/oracle/product/11.2.0.1/db_home1/rdbms/admin/utldtree.sql
BEGIN
  deptree_fill(name => 'OSS_PRODUCT_MID_DAY_201109',schema => 'OSS03',type => 'TABLE');
END;
SELECT * FROM ideptree;
ideptree 这个视图了

exec deptree_fill('TABLE','SCOTT','DEPT');

--依赖个人版:
select * from Dba_Objects where object_name like '%OSS_SP_MAIL_BATCH_TOTAL_STAT%'
BEGIN
  deptree_fill(name => 'OSS_SP_MAIL_BATCH_TOTAL_STAT',schema => 'OSS03',type => 'PROCEDURE');
END;

   select d.nest_level, o.object_type, o.owner, o.object_name, d.seq#,o.object_name
  from deptree_temptab d, dba_objects o
  where d.object_id = o.object_id (+)
union all
  select d.nest_level+1, 'CURSOR', '<shared>', '"'||c.kglnaobj||'"', d.seq#+.5,g.kglnaobj
  from deptree_temptab d, x$kgldp k, x$kglob g, obj$ o, user$ u, x$kglob c,
      x$kglxs a
    where d.object_id = o.obj#
    and   o.name = g.kglnaobj
    and   o.owner# = u.user#
    and   u.name = g.kglnaown
    and   g.kglhdadr = k.kglrfhdl
    and   k.kglhdadr = a.kglhdadr   /* make sure it is not a transitive */
    and   k.kgldepno = a.kglxsdep   /* reference, but a direct one */
    and   k.kglhdadr = c.kglhdadr
    and   c.kglhdnsp = 0
select d.nest_level
x$kgldp k, x$kglob g, obj$ o, user$ u, x$kglob c,
      x$kglxs a

--在线重定义失败后的索引恢复:
DECLARE
isClean BOOLEAN;
BEGIN
  isClean := FALSE;
  WHILE isClean=FALSE
  LOOP
    isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id, dbms_repair.lock_wait);
    dbms_lock.sleep(5);
  END LOOP;
END;