ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to know one object's dependence?

How to know one object's dependence?

原创 Linux操作系统 作者:hrb_qiuyb 时间:2008-01-31 08:13:30 0 删除 编辑

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

一、以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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9134/viewspace-166494/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2008-01-30

  • 博文量
    50
  • 访问量
    534900