MANAGING DEPENDENCIES IN ORACLE 11G WITH EXAMPLES
For PPTs click on the below link
Managing Dependencies PPT and Demos
/*
USER_DEPENDENCIES view
The dependency matrix of an object is maintained by [ALL | DBA | USER]_DEPENDENCIES
dictionary view in Oracle.
It captures the referenced object’s name, type, owner, and db link information.
*/
Desc USER_DEPENDENCIES;
--
select * from USER_DEPENDENCIES;
---
--
select name, type, referenced_name, referenced_type
from user_dependencies
where referenced_owner = user;
----
/*
If you're really dealing with just PL/SQL packages you do not need to sweat
the build order. Just build all the package specifications first.
Then you can deploy all the package bodies and they will compile,
because their dependencies are the package specs.
If you happen to have some package specs which do depend on other specs -
if you have packages which declare, say, constants, subtypes or ref cursors
which are used in the signatures of packaged procedures -
then you need to build those package specs first.
But there should be few enough of them
that you can arrange them in the build script by hand.
*/
--The interesting thing is that a procedure in PKG1 calls a procedure from PKG2,
--a procedure in PKG2 calls a procedure from PKG3
--and a procedure in PKG3 calls a procedure from PKG1.
--pkg3 spec.
create or replace package pkg3 is
procedure p5;
procedure p6;
end pkg3;
-----
--pkg2 spec.
create or replace package pkg2 is
procedure p3;
procedure p4;
end pkg2;
----
--pkg1 spec.
create or replace package pkg1 is
procedure p1;
procedure p2;
end pkg1;
-----
--pkg2 body.
create or replace package body pkg2 is
procedure p3 is
begin
pkg3.p5;
end p3;
procedure p4 is
begin
dbms_output.put_line('PKG2.P4');
end p4;
end pkg2;
----
--pkg3 body.
create or replace package body pkg3 is
procedure p5 is
begin
dbms_output.put_line('PKG3.P5');
end p5;
procedure p6 is
begin
pkg1.p1;
end p6;
end pkg3;
/*
1)PKG1 calls PKG2 => DIRECT dependencie between PKG1 and PKG2
2)PKG1 calls PKG2 and PKG2 calls PKG3 => INDIRECT dependencie between PKG1 and PKG3
*/
--pkg1 body.
create or replace package body pkg1 is
procedure p1 is
begin
dbms_output.put_line('PKG1.P1');
end p1;
procedure p2 is
begin
pkg2.p4;
end p2;
end pkg1;
----
--I have a schema with three packages....
--LETS CHECK
select object_name, object_type, status
from user_objects --where object_name like 'PKG_'
order by 1, 2;
---
--TO DISPLAY THE DEPENDENCY FOR THE PARTICULAR OBJECT
--exec dbms_utility.get_dependency(OBJECT_TYPE,USER,OBJECT_NAME)
exec dbms_utility.get_dependency('PACKAGE', 'SYSTEM', 'PKG1');
--
--The following query shows which object types in your database
--are dependent on other objects:
SELECT DISTINCT TYPE
FROM DBA_DEPENDENCIES
ORDER BY TYPE;
--
--The following query shows which object types in your database are referenced by other objects:
SELECT DISTINCT REFERENCED_TYPE
FROM DBA_DEPENDENCIES
ORDER BY REFERENCED_TYPE;
--CHECKING THE STATUS OF DEPENDENT OBJECT IF MODIFICATION MADE IN REFERENCED OBJECT
CREATE OR REPLACE VIEW commissioned AS
SELECT first_name, last_name, commission_pct FROM employees
WHERE commission_pct > 0.00;
--
CREATE OR REPLACE VIEW sixfigures AS
SELECT * FROM employees
WHERE salary >= 10000;
--
select object_name, status from user_objects where object_type = 'VIEW';
--Schema Object Change that Invalidates Some Dependents
CREATE OR REPLACE VIEW commissioned AS
SELECT first_name, last_name, commission_pct FROM employees
WHERE commission_pct > 0.00;
CREATE OR REPLACE VIEW sixfigures AS
SELECT * FROM employees
WHERE salary >= 100000;
--
select object_name, status from user_objects where object_type = 'VIEW';
---Suppose you determine that the EMAIL column in the EMPLOYEES
--table must be lengthened. You alter the table as follows:
ALTER TABLE employees MODIFY email VARCHAR2(100);
/*
If you alter the definition of a referenced object,
dependent objects might or might not continue to function without error,
depending on the type of alteration.
*/select object_name, status from user_objects where object_type = 'VIEW';
---Because the COMMISSIONED view does not include EMAIL in its select list,
---it is not invalidated.
--However, because the SIXFIGURES view selects all columns in the table,
--it is invalidated.---
--
create or replace package pkg4 is
procedure p7;
end pkg4;
create or replace package body pkg4 is
procedure p7 is
begin
dbms_output.put_line('PKG4.P7::'||constants_pkg.whatever);
end p7;
end pkg4;
---
--PKG4 is INVALID because we have not built CONSTANTS_PKG yet.
--
create or replace package constants_pkg is
whatever constant varchar2(20) := 'WHATEVER';
end constants_pkg;
--
select object_name, object_type, status
from user_objects
where status != 'VALID'
order by 1, 2;
--PKG4 is INVALID because we have TO built pkg4 AGAIN.
--
create or replace package pkg4 is
procedure p7;
end pkg4;
--
create or replace package body pkg4 is
procedure p7 is
begin
dbms_output.put_line('PKG4.P7::'||constants_pkg.whatever);
end p7;
end pkg4;
---
set serveroutput on size unlimited;
exec pkg4.p7;
---
--One little thing to watch out for when walking the dependency tree.
--Dependencies for uncompiled programs don't show...
DROP package constants_pkg
--
create or replace package body pkg4 is
procedure p7 is
begin
dbms_output.put_line('PKG4.P7::'||constants_pkg.whatever);
end p7;
end pkg4;
--
create or replace package constants_pkg is
whatever constant varchar2(20) := 'WHATEVER';
end constants_pkg;
--
select object_name, object_type, status
from user_objects
where status != 'VALID'
order by 1, 2;
--
--RECOMPILING PACKAGES
alter package pkg4 compile body;
/*Querying Object Dependencies
The static data dictionary views USER_DEPENDENCIES, ALL_DEPENDENCIES,
and DBA_DEPENDENCIES describe dependencies between database objects.
The utldtree.sql SQL script creates the view DEPTREE,
which contains information on the object dependency tree,
and the view IDEPTREE, a presorted, pretty-print version of DEPTREE.
*/
/*
Track Direct and Indirect dependency views
Besides USER_DEPENDENCIES dictionary view, Oracle provides two additional views to track direct and indirect dependency of the object. These views are DEPTREE (direct dependencies) and IDEPTREE (indirect dependencies), which do not exist in the schema as other dictionary view, but their script can be found in ORACLE_HOME.
Steps to follow
1. Execute the UTLDTREE.sql script from ORACLE_HOME/rdbms/admin folder.
2. Execute the DEPTREE_FILL procedure to populate the DEPTREE_TEMPTAB table.
*/
--How to use?
--Suppose, I want to check the dependency level of a procedure P7,
--I would execute the DEPTREE_FILL procedure in the below fashion.
EXEC DEPTREE_FILL('TABLE','SYSTEM','EMPLOYEES');
EXEC DEPTREE_FILL('PACKAGE','SYSTEM','PKG2');
SELECT * FROM ideptree;SELECT * FROM deptree;
/*
Above statement would populate the dependency matrix into DEPTREE_TEMPTAB table.
The view DEPTREE AND IDEPTREE are created on top of DEPTREE_TEMPTAB.*/
SELECT NESTED_LEVEL,TYPE,NAME
FROM DEPTREE;
ORDER BY seq#;
--
--Compile Procedure ACCESS_RIGHTS located in MYUSER schema.
ALTER PACKAGE SYSTEM.PKG1
COMPILE PACKAGE;
ALTER PACKAGE SYSTEM.PKG1
COMPILE BODY;
ALTER TRIGGER SYSTEM.T1
COMPILE ;
Alter trigger SYSTEM.T1 DISABLE;
Alter trigger SYSTEM.T1 ENABLE;
ALTER TRIGGER SYSTEM.T1 RENAME TO SYSTEM.TRIGGER1;
--
EXEC DBMS_UTILITY.compile_schema(USER, FALSE);
SELECT name, type, referenced_name, referenced_type
FROM sys.dba_dependencies
WHERE owner = 'SYSTEM';
No comments:
Post a Comment