oracle如何打开object,Oracle如何查看表被数据库中那些object使用

今天看到一个很有趣的问题，怎么查看一个table被数据库中那些object使用？原问题的链接如下：

http://www.itpub.net/forum.php?mod=viewthread&tid=1849997&extra=page%3D1%26filter%3Dtypeid%26typeid%3D37%26typeid%3D37

出于好奇，我搜索了下，个人认为可以很好的回答上面的问题。

(一)正常情况(无动态SQL时)

参考了链接：

http://www.oratable.com/which-objects-refer-to-this-table/

System/Sysdba用户，可以使用以下SQL查看：

select * from dba_dependencies where referenced_name =upper('emp') and owner=upper('tmd')

普通用户可以使用以下SQL查看：

select * from all_dependencies where referenced_name =upper('emp')

上面的区别是可以看的用户范围不同：

DBA_DEPENDENCIES describes all dependencies between objects in the database. This view may only be accessible to users with DBA rights.

USER_DEPENDENCIES describes dependencies between objects only in the current user’s schema. This view does not have the column OWNER, as it is contained in the view definition.

上面的运行结果如下：

上面的缺点是如果表名在字符串里面，也就是存在动态执行sql的情况，则得不到对象名称。例子如下：

select * from all_dependencies where name like '%PROC_DBMS%' and referenced_name =upper('emp')

结果如下：

(二)动态SQL情况

对于动态sql只能从源码中查询了。参考了链接：

https://community.oracle.com/thread/517948

System/Sysdba可以使用下面的SQL查询：

select name,type,text from dba_source where upper(text) like '%EMP%' and owner=upper('tmd')

普通用户可以使用下面的SQL查询：

select name,type,text from user_source where upper(text) like '%EMP%'

以上面的例子为例：

select name,type,text from user_source where upper(text) like '%EMP%' and name like 'PROC_DBMS%'

结果如下：

查看所有引用表的动态sql：

System/Sysdba用户：

select name, type, text

from dba_source

where name in (select name

from dba_source

where upper(text) like '%EMP%'

and owner = upper('tmd')

minus

select name

from dba_dependencies

where referenced_name = upper('emp')

and owner = upper('tmd'))

and owner = upper('tmd')

普通用户：

select name, type, text

from user_source

where name in (select name

from user_source

where upper(text) like '%EMP%'

minus

select name

from all_dependencies

where referenced_name = upper('emp'))

如果有朋友说，我只想要看表被那些表外键引用了，怎么办呢？

参考了链接：

http://stackoverflow.com/questions/1143728/how-can-i-find-which-tables-reference-a-given-table-in-oracle-sql-developer

查看外键引用的SQL如下：

select table_name, constraint_name, status, owner

from all_constraints

where r_owner = 'TMD'

and constraint_type = 'R'--foreign key

and r_constraint_name in (select constraint_name

from all_constraints

where constraint_type in ('P', 'U')--pk unique key

--and table_name = 'DEPT'

and owner = 'TMD')

order by table_name, constraint_name

希望对各位有用。

全文完。