Oracle PL/SQL/System Tables Views/user dependencies
Содержание
Check dependencies for procedure
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> create or replace procedure P1 is
2 v_cnt number;
3 begin
4 select count(*) into v_cnt from emp;
5 end;
6 /
SQL> create or replace procedure P2 is
2 begin
3 P1;
4 end;
5 /
SQL> create or replace procedure P3 is
2 begin
3 P2;
4 end;
5 /
SQL> create or replace procedure P4 is
2 begin
3 P3;
4 end;
5 /
SQL>
SQL> select name, type,referenced_name, referenced_type from user_Dependencies where rownum < 20
2 /
Enter...
SQL>
Check package reference
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> create or replace view v as select * from t;
View created.
SQL>
SQL> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t )
5 loop
6 null;
7 end loop;
8 end;
9 /
SQL>
SQL> create or replace function f return number
2 as
3 countValue number;
4 begin
5 select count(*) into countValue from t;
6 return countValue;
7 end;
8 /
Function created.
SQL>
SQL>
SQL> create or replace procedure p2
2 as
3 begin
4 p;
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL>
SQL> create or replace package p1
2 as
3 procedure p;
4 end;
5 /
SQL>
SQL> create or replace package body p1
2 as
3 procedure p
4 as
5 begin
6 for x in ( select * from t )
7 loop
8 null;
9 end loop;
10 end;
11 end p1;
12 /
Warning: Package Body created with compilation errors.
SQL>
SQL> create or replace package p2
2 as
3 procedure p;
4 end;
5 /
SQL>
SQL> create or replace package body p2
2 as
3 procedure p
4 as
5 begin
6 p1.p;
7 end;
8 end p2;
9 /
Warning: Package Body created with compilation errors.
SQL>
SQL> select name, type, referenced_name, referenced_type
2 from user_dependencies
3 where referenced_owner = user and rownum < 20
4 order by name
5 /
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
COMPILEERROR PROCEDURE
DUAL
NON-EXISTENT
COMPILE_ERROR PROCEDURE
DUAL
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
DEFINER_RIGHTS PACKAGE BODY
DUAL
NON-EXISTENT
LOOPING_EXAMPLE PROCEDURE
DBMS_OUTPUT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
NON-EXISTENT
MONTH_GENERATOR FUNCTION
SQLMONTH_TABLETYPE
TYPE
MONTH_GENERATOR_PIPED FUNCTION
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
SQLMONTH_TABLETYPE
TYPE
ORD_GENERATOR FUNCTION
ORD_TYPE
TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
ORD_GENERATOR FUNCTION
ORD_TABLE
TYPE
ORD_GENERATOR_PIPED FUNCTION
ORD_TABLE
TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
ORD_GENERATOR_PIPED FUNCTION
ORD_TYPE
TYPE
ORD_TABLE TYPE
ORD_TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
TYPE
PRODUCT_PACKAGE PACKAGE BODY
PRODUCT_PACKAGE
PACKAGE
P_ADD_ORDERS PROCEDURE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
DUAL
NON-EXISTENT
P_ADD_ORDERS PROCEDURE
ORDER_SEQ
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
P_ADD_ORDERS PROCEDURE
P_ADD_ITEMS
PROCEDURE
P_ADD_PROD PROCEDURE
PRODUCT_SEQ
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
READCLOB PROCEDURE
DBMS_OUTPUT
NON-EXISTENT
TEMPERATURE_CONVERSION PROCEDURE
DBMS_OUTPUT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
NON-EXISTENT
USE_G2 FUNCTION
DUAL
NON-EXISTENT
19 rows selected.
Check user dependencies for a given table
SQL> select name, type,
2 referenced_name, referenced_type
3 from user_Dependencies
4 where name = "EMP_MAINT"
5 /
no rows selected
SQL>
Query user_dependencies for cross references
SQL>
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> create or replace view v as select * from t;
View created.
SQL>
SQL> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t )
5 loop
6 null;
7 end loop;
8 end;
9 /
SQL>
SQL> create or replace function f return number
2 as
3 countValue number;
4 begin
5 select count(*) into countValue from t;
6 return countValue;
7 end;
8 /
Function created.
SQL>
SQL> select name, type, referenced_name, referenced_type
2 from user_dependencies
3 where referenced_owner = user and rownum < 20
4 order by name
5 /
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
COMPILEERROR PROCEDURE
DUAL
NON-EXISTENT
COMPILE_ERROR PROCEDURE
DUAL
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
DEFINER_RIGHTS PACKAGE BODY
DUAL
NON-EXISTENT
LOOPING_EXAMPLE PROCEDURE
DBMS_OUTPUT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
NON-EXISTENT
MONTH_GENERATOR FUNCTION
SQLMONTH_TABLETYPE
TYPE
MONTH_GENERATOR_PIPED FUNCTION
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
SQLMONTH_TABLETYPE
TYPE
ORD_GENERATOR FUNCTION
ORD_TYPE
TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
ORD_GENERATOR FUNCTION
ORD_TABLE
TYPE
ORD_GENERATOR_PIPED FUNCTION
ORD_TABLE
TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
ORD_GENERATOR_PIPED FUNCTION
ORD_TYPE
TYPE
ORD_TABLE TYPE
ORD_TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
TYPE
PRODUCT_PACKAGE PACKAGE BODY
PRODUCT_PACKAGE
PACKAGE
P_ADD_ORDERS PROCEDURE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
DUAL
NON-EXISTENT
P_ADD_ORDERS PROCEDURE
ORDER_SEQ
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
P_ADD_ORDERS PROCEDURE
P_ADD_ITEMS
PROCEDURE
P_ADD_PROD PROCEDURE
PRODUCT_SEQ
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
READCLOB PROCEDURE
DBMS_OUTPUT
NON-EXISTENT
TEMPERATURE_CONVERSION PROCEDURE
DBMS_OUTPUT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
NON-EXISTENT
USE_G2 FUNCTION
DUAL
NON-EXISTENT
19 rows selected.
SQL>
SQL> drop table t;
Table dropped.
Who referenced current user"s objects
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> create or replace view v as select * from t;
View created.
SQL>
SQL> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t )
5 loop
6 null;
7 end loop;
8 end;
9 /
SQL>
SQL> create or replace function f return number
2 as
3 countValue number;
4 begin
5 select count(*) into countValue from t;
6 return countValue;
7 end;
8 /
Function created.
SQL>
SQL>
SQL> create or replace procedure p2
2 as
3 begin
4 p;
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL>
SQL>
SQL> select name, type, referenced_name, referenced_type
2 from user_dependencies
3 where referenced_owner = user and rownum < 20
4 order by name
5 /
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
COMPILEERROR PROCEDURE
DUAL
NON-EXISTENT
COMPILE_ERROR PROCEDURE
DUAL
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
DEFINER_RIGHTS PACKAGE BODY
DUAL
NON-EXISTENT
LOOPING_EXAMPLE PROCEDURE
DBMS_OUTPUT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
NON-EXISTENT
MONTH_GENERATOR FUNCTION
SQLMONTH_TABLETYPE
TYPE
MONTH_GENERATOR_PIPED FUNCTION
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
SQLMONTH_TABLETYPE
TYPE
ORD_GENERATOR FUNCTION
ORD_TYPE
TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
ORD_GENERATOR FUNCTION
ORD_TABLE
TYPE
ORD_GENERATOR_PIPED FUNCTION
ORD_TABLE
TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
ORD_GENERATOR_PIPED FUNCTION
ORD_TYPE
TYPE
ORD_TABLE TYPE
ORD_TYPE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
TYPE
PRODUCT_PACKAGE PACKAGE BODY
PRODUCT_PACKAGE
PACKAGE
P_ADD_ORDERS PROCEDURE
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
DUAL
NON-EXISTENT
P_ADD_ORDERS PROCEDURE
ORDER_SEQ
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
P_ADD_ORDERS PROCEDURE
P_ADD_ITEMS
PROCEDURE
P_ADD_PROD PROCEDURE
PRODUCT_SEQ
NON-EXISTENT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
READCLOB PROCEDURE
DBMS_OUTPUT
NON-EXISTENT
TEMPERATURE_CONVERSION PROCEDURE
DBMS_OUTPUT
NAME TYPE
------------------------------ -----------------
REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
NON-EXISTENT
USE_G2 FUNCTION
DUAL
NON-EXISTENT
19 rows selected.
SQL>
SQL> drop table t;
Table dropped.