Know Depandencies

Dependencies

Displaying direct and Indirect dependencies
Step 1 – Run the script utldtree.sql that creates the objects that enable you to display the direct and indirect dependencies.
(This script is in ORACLE_HOME/rdbms/admin folder)

Scenario – Table is emp
EMP table has two direct dependent objects of type view and trigger.

create or replace view V_Emp
as
select * from emp
where job = ‘MANAGER’
/


create or replace trigger E_Trig
after insert on emp
for each row
begin
dbms_output.put_line(‘Record inserted in emp table’);
end;
/

There is a procedure P_V_Emp which is based on  view V_Emp.
So there is indirect dependency of table emp with P_V_Emp procedure.

create or replace procedure P_V_Emp(no emp1.empno%type, name emp1.ename%type, j emp1.job%type, s emp1.sal%type DEFAULT ‘MANAGER’)
is
begin
Insert into V_Emp(empno,ename,job,sal)
values(no,name,j,s);
end;
/

Now there is a procedure P2 which calls P_V_Emp
create or replace procedure p2
is
begin
P_V_Emp(123,’abc’, 8000);
end;
/

To get the list of dependencies execute the procedure deptree_fill

EXECUTE deptree_fill(‘TABLE’,’SCOTT’,’EMP’);
DEPTREE VIEW
select nested_level, type, name
from deptree
/

p8

To display an indented representation of all the dependent objects query ideptree view.

p9

 

 

 

Posted in UncategorizedTagged