一点一滴努力,满仓满谷收成

在我们的日常的维护中，有时我们会调整一张表的表结构或是修改某一个底层一些的procedure等对象，而对其有依赖性的一些对象就会更得无效。事实上我们常需要看一下有哪些Object对于要修改的这个Object有依赖性以确实影响的范围，从而确定修改的时间。[@more@]

1 以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;

2 发现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。

1)查看一下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#)

2)以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.

3)测试一下。

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.

4)显示结果

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