Oracle 检查对象的依赖及被依赖性

--检查对象的依赖及被依赖性

--数据库日常操作中，经常要对object做操作，但是数据库中object是有依赖性的，

--如果一个不小心，就可能导致对象失效，所以在做这种操作的时候，要特别注意，

--下面提供两个脚本，来检测对象的依赖性以及被依赖性：

    

--查出某个object依赖的其他对象,比如一个VIEW,用该SQL可以查出这个视图所依赖的表啊什么的

SELECT a.referenced_type AS type,

SUBSTR(a.referenced_owner,1,10) AS ref_owner,

a.referenced_name AS ref_name,

SUBSTR(a.referenced_link_name,1,20) AS ref_link_name

FROM all_dependencies a

WHERE  a.name  = Upper('&object_name')

AND a.owner = DECODE(UPPER('&owner'), 'ALL', a.referenced_owner, UPPER('&owner'))

ORDER BY 1,2,3;

--查出某个object被哪些对象依赖,就是检查你要修改的object被哪些对象引用了等等.

SELECT a.type,

SUBSTR(a.owner,1,10) AS owner,

a.name

FROM  all_dependencies a

WHERE  a.referenced_name  = UPPER('&object_name')

AND a.referenced_owner = DECODE(UPPER('&owner'), 'ALL', a.referenced_owner, UPPER('&owner'))

ORDER BY 1,2,3;

--查看数据库对象是否被锁

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

set wrap off

set linesize 200

col username for a10

col logon_time for a20

col lock_level for a10

col owner for a10

col object_name for a30

col object_type for a15

col status for a10

col program for a30

col osuser for a10

SELECT /*+ rule */ s.username,s.logon_time,

decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) "LOCK_LEVEL",

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,p.spid,s.status,s.program,s.osuser

FROM v$session s,v$lock l,dba_objects o,v$process p

WHERE l.sid=s.sid AND p.addr=s.paddr and o.owner='&object_owner' and o.object_name='&object_name' and l.id1=o.object_id(+) AND s.username is NOT Null;