一、以HR用户下的Table A进行测试，建一Procedure参照它。

SQL> select * from tab where tname='A';

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE

CREATE PROCEDURE "HR"."TEST" as
v1 number;
begin
select count(*) into v1 from a;
end;

二、发现dependence的方法。

1、最简单的方法：使用Dba_dependencies这个视图。

SQL> select name,REFERENCED_OWNER,REFERENCED_TYPE,REFERENCED_NAME from user_dependencies where REFERENCED_NAME='A';

NAME REFERENCED REFERENCED_TYPE REFERENCED_NAME
-------- ---------- ----------------- ---------------
TEST HR TABLE A

2、最直观的方法：使有Toad

登陆Toad->view->dependencies 看到界面你就明白怎么做了。

3、最标准的做法:utldtree.sql,通过这种方法还可以发现内存中的哪些Cursor在用这个Object。

a、查看一下utldtree.sql

oracle@yang:~> cd /opt/oracle/product/9ir2/rdbms/admin

oracle@yang:/opt/oracle/product/9ir2/rdbms/admin> ls -l *tree*
-rw-r--r-- 1 oracle dba 4766 2002-03-08 23:28 utldtree.sql

主要用到以下两个Object,deptree_fill这个procedure用于填充依赖性关系表,查deptree这个视图就可以以可读的显示显示出依赖性关系。
create or replace procedure deptree_fill (type char, schema char, name char)

create view sys.deptree
(nested_level, type, schema, name, seq#)

b、以sys用户运行下utldtree.sql
SQL> @utldtree.sql
drop sequence deptree_seq
*
ERROR at line 1:
ORA-02289: sequence does not exist

Sequence created.

drop table deptree_temptab
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Procedure created.

drop view deptree
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> REM This view will succeed if current user is sys. This view shows
SQL> REM which shared cursors depend on the given object. If the current
SQL> REM user is not sys, then this view get an error either about lack
SQL> REM of privileges or about the non-existence of table x$kglxs.
SQL>
SQL> set echo off

View created.

SQL>
SQL> REM This view will succeed if current user is not sys. This view
SQL> REM does *not* show which shared cursors depend on the given object.
SQL> REM If the current user is sys then this view will get an error
SQL> REM indicating that the view already exists (since prior view create
SQL> REM will have succeeded).
SQL>
SQL> set echo off
create view deptree
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

drop view ideptree
*
ERROR at line 1:
ORA-00942: table or view does not exist

View created.

c、测试一下。

SQL> desc deptree_fill
PROCEDURE deptree_fill
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TYPE CHAR IN
SCHEMA CHAR IN
NAME CHAR IN

SQL> execute deptree_fill('TABLE','HR','A');

PL/SQL procedure successfully completed.

d、显示结果
SQL> col name format a40
SQL> col schema format a20
SQL> select * from deptree;

NESTED_LEVEL TYPE SCHEMA NAME SEQ#
------------ ------------------ -------------------- ---------------- ----------
0 TABLE HR A 0
1 PROCEDURE HR TEST 1