select a.object_type, a.object_name, b.owner, b.object_type, b.object_name, b.object_id , b.status from dba_objects a , dba_objects b , (select object_id, referenced_object_id from public_dependency start with object_id = (select object_id from dba_objects where owner = &owner and object_name = &name and object_type = &type) connect by prior referenced_object_id = object_id) c where a.object_id = c.object_id and b.object_id = c.referenced_object_id;
这样能看出来存储过程test依赖于表aa吗?只有在编译这个存储过程的时候,才能知道。 要不就是读出这个存储过程的源码。。但是如果过程被加密了、、还能知道吗?我不知道被加密的 过程能不能有效的解密。。SQL> desc aa; 名称 是否为空? 类型 ----------------------------------------- -------- ------------ NAME VARCHAR2(8) create or replace procedure test as mystr varchar2(50); begin update aa set name=''; end; /
to ATGC:测试如下: SQL> create table aa (name varchar2(30)); Table created.SQL> create or replace procedure test 2 as 3 mystr varchar2(50); 4 begin 5 update aa set name=''; 6 end; 7 / Procedure created.SQL> SET pages 999 SQL> select a.object_type, a.object_name, b.owner, b.object_type 2 , b.object_name, b.object_id, b.status 3 from dba_objects a 4 , dba_objects b 5 , (select object_id, referenced_object_id 6 from public_dependency 7 start with object_id = 8 (select object_id 9 from dba_objects 10 where owner = 'STSC' 11 and object_name = 'TEST' 12 and object_type = 'PROCEDURE') 13 connect by prior referenced_object_id = object_id) c 14 where a.object_id = c.object_id 15 and b.object_id = c.referenced_object_id 16 and a.owner not in ('SYS') 17 and b.owner not in ('SYS');OBJECT_TYPE ------------------ OBJECT_NAME -------------------------------------------------------------------------------- OWNER OBJECT_TYPE ------------------------------ ------------------ OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID STATUS ---------- ------- PROCEDURE TEST STSC TABLE AA 81217 VALID
弱水的方法好。。刚刚测试了一下,但 如果TEST是INVALID状态的,就不能查出来 比如:SQL> select substrb(object_name,1,4) object_name,object_type,status 2 from dba_objects where object_name='TEST' 3 /OBJE OBJECT_TYPE STATUS ---- ------------------ ------- TEST PROCEDURE INVALIDSQL> select a.object_type, a.object_name, b.owner, b.object_type 2 , b.object_name, b.object_id, b.status 3 from dba_objects a 4 , dba_objects b 5 , (select object_id, referenced_object_id 6 from public_dependency 7 start with object_id = 8 (select object_id 9 from dba_objects 10 where owner = 'SYSTEM' 11 and object_name = 'TEST' 12 and object_type = 'PROCEDURE') 13 connect by prior referenced_object_id = object_id) c 14 where a.object_id = c.object_id 15 and b.object_id = c.referenced_object_id 16 and a.owner not in ('SYS') 17 and b.owner not in ('SYS') 18 /未选定行
===========================
不容易知道,要知道比较麻烦
也不是不能知道假如我在存储过程里写这么一句
update table set field=1;
我们怎么可能知道这个存储过程依存于哪张表。。
唯一得办法就是从数据字典里把存储过程的源码读出来,一行一行的看,才能知道
那如果这个存储过程被加密了咋办?
, b.status
from dba_objects a
, dba_objects b
, (select object_id, referenced_object_id
from public_dependency
start with object_id =
(select object_id
from dba_objects
where owner = &owner and object_name = &name
and object_type = &type)
connect by prior referenced_object_id = object_id) c
where a.object_id = c.object_id and b.object_id = c.referenced_object_id;
要不就是读出这个存储过程的源码。。但是如果过程被加密了、、还能知道吗?我不知道被加密的
过程能不能有效的解密。。SQL> desc aa;
名称 是否为空? 类型
----------------------------------------- -------- ------------
NAME VARCHAR2(8)
create or replace procedure test
as
mystr varchar2(50);
begin
update aa set name='';
end;
/
SQL> create table aa (name varchar2(30));
Table created.SQL> create or replace procedure test
2 as
3 mystr varchar2(50);
4 begin
5 update aa set name='';
6 end;
7 /
Procedure created.SQL> SET pages 999
SQL> select a.object_type, a.object_name, b.owner, b.object_type
2 , b.object_name, b.object_id, b.status
3 from dba_objects a
4 , dba_objects b
5 , (select object_id, referenced_object_id
6 from public_dependency
7 start with object_id =
8 (select object_id
9 from dba_objects
10 where owner = 'STSC'
11 and object_name = 'TEST'
12 and object_type = 'PROCEDURE')
13 connect by prior referenced_object_id = object_id) c
14 where a.object_id = c.object_id
15 and b.object_id = c.referenced_object_id
16 and a.owner not in ('SYS')
17 and b.owner not in ('SYS');OBJECT_TYPE
------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OWNER OBJECT_TYPE
------------------------------ ------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID STATUS
---------- -------
PROCEDURE
TEST
STSC TABLE
AA
81217 VALID
如果TEST是INVALID状态的,就不能查出来
比如:SQL> select substrb(object_name,1,4) object_name,object_type,status
2 from dba_objects where object_name='TEST'
3 /OBJE OBJECT_TYPE STATUS
---- ------------------ -------
TEST PROCEDURE INVALIDSQL> select a.object_type, a.object_name, b.owner, b.object_type
2 , b.object_name, b.object_id, b.status
3 from dba_objects a
4 , dba_objects b
5 , (select object_id, referenced_object_id
6 from public_dependency
7 start with object_id =
8 (select object_id
9 from dba_objects
10 where owner = 'SYSTEM'
11 and object_name = 'TEST'
12 and object_type = 'PROCEDURE')
13 connect by prior referenced_object_id = object_id) c
14 where a.object_id = c.object_id
15 and b.object_id = c.referenced_object_id
16 and a.owner not in ('SYS')
17 and b.owner not in ('SYS')
18 /未选定行