Table of Contents
MANAGING DEPENDENCIES IN ORACLE
WHAT IS DEPENDENCY?
If the definition of object OB1 references object OB2 then OB1 is dependent on OB2. Oracle database automatically track and manages these dependencies. If you alter the definition of reference object OB2 then dependent object OB1 may or may not work..
CREATE TABLE EMPLOYEES_COPY AS SELECT * FROM EMPLOYEES;
CREATE OR REPLACE VIEW VW_EMPLOYEES_COPY AS SELECT * FROM EMPLOYEES_COPY;
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'VW_EMPLOYEES_COPY';
In the above example, we have created the table EMPLOYEES_COPY and then have created the view VW_EMPLOYEES_COPY from EMPLOYEES_COPY table. USER_OBJECTS table is showing the status as VALID. Now we will drop the table EMPLOYEES_COPY to see the impact.
DROP TABLE EMPLOYEES_COPY;
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'VW_EMPLOYEES_COPY';
Now since the reference object is dropped the view VW_EMPLOYEES_COPY becomes uncompiled.
Now in another example we will check the impact of ALTER command.
CREATE TABLE DEPARTMENTS_COPY AS SELECT * FROM DEPARTMENTS;
CREATE OR REPLACE VIEW VW_DEPARTMENTS_COPY AS SELECT * FROM DEPARTMENTS_COPY;
CREATE OR REPLACE VIEW VW_DEPARTMENTS_COPY AS SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS_COPY;
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'VW_DEPARTMENTS_COPY';
ALTER TABLE DEPARTMENTS_COPY ADD (DEPARTMENT_COUNT NUMBER);
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'VW_DEPARTMENTS_COPY';
ALTER TABLE DEPARTMENTS_COPY MODIFY (DEPARTMENT_NAME VARCHAR2(40));
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'VW_DEPARTMENTS_COPY';
In the above example, we can see that when we alter the DEPARTMENTS_COPY for adding a new column DEPARTMENT_COUNT then the view status was VALID. But when we modify the column DEPARTMENT_NAME then the view status was INVALID. This is due to DEPARTMENT_NAME was part of view definition.
TYPES OF DEPENDENCY
Oracle automatically tracks dependencies and performed necessary recompilation.
- DIRECT DEPENDENCY
- INDIRECT DEPENDENCY
- LOCAL DEPENDENCY
- REMOTE DEPENDENCY
DIRECT DEPENDENCY
When one object is directly dependent on other object then its call DIRECT DEPENDENCY.
CREATE OR REPLACE VIEW VW_EMPLOYEES_COPY_1 AS SELECT * FROM EMPLOYEES_COPY;
CREATE OR REPLACE VIEW VW_EMPLOYEES_COPY_2 AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID FROM EMPLOYEES_COPY;
CREATE OR REPLACE PROCEDURE EMPLOYEES_COPY_PROC
IS
BEGIN
FOR I IN (SELECT * FROM EMPLOYEES_COPY)
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||I.EMPLOYEE_ID||' FIRST_NAME -> '||I.FIRST_NAME||' JOB_ID -> '||I.JOB_ID);
END LOOP;
END;
/
In the above example, we can see that the views VW_EMPLOYEES_COPY_1, VW_EMPLOYEES_COPY_2 and procedure EMPLOYEES_COPY_PROC are dependent on table EMPLOYEES_COPY. This is called DIRECT DEPENDENCY.
SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME = 'EMPLOYEES_COPY';
INDIRECT DEPENDENCY
When an object is indirectly dependent on another object that is called INDIRECT DEPENDENCY.
CREATE OR REPLACE PROCEDURE EMPLOYEES_COPY_PROC_IN
IS
BEGIN
FOR I IN (SELECT * FROM VW_EMPLOYEES_COPY_1)
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||I.EMPLOYEE_ID||' FIRST_NAME -> '||I.FIRST_NAME||' JOB_ID -> '||I.JOB_ID);
END LOOP;
END;
/
In the above example, procedure EMPLOYEES_COPY_PROC_IN is directly dependent on view VW_EMPLOYEES_COPY_1 but indirectly dependent on table EMPLOYEES_COPY.
SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME = 'EMPLOYEES_COPY' UNION SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME = 'VW_EMPLOYEES_COPY_1';
So we can see that the procedure EMPLOYEES_COPY_PROC_IN is directly dependent on view VW_EMPLOYEES_COPY_1 but indirectly dependent on table EMPLOYEES_COPY, because the view VW_EMPLOYEES_COPY_1 is directly dependent on table EMPLOYEES_COPY.
LOCAL DEPENDENCY
If dependency among objects occurs in a single database then it's called LOCAL DEPENDENCY. The example described above is called LOCAL DEPENDENCY as all the objects are referred in single database.
REMOTE DEPENDENCY
Like LOCAL DEPENDENCY oracle also manages the dependency between distributed database objects. It is called REMOTE DEPENDENCY.
A procedure or function can directly or indirectly reference:
- TABLES
- VIEWS
- SEQUENCE
- PROCEDURE
- FUNCTION
- PACKAGED PROCEDURE
- PACKAGED FUNCTION
DISPLAYING DIRECT & INDIRECT DEPENDENCIES
To display the DIRECT and INDIRECT DEPENDENCY there is a script available in ORACLE_HOME/rdbms/admin folder. The name of the script is utldtree.sql.
We will execute the script utldtree.sql to view the DIRECT and INDIRECT DEPENDENCY
@'C:\WINDOWS.X64_193000_db_home\rdbms\admin\utldtree.sql';
Please ignore the errors after executing the script.
EXEC DEPTREE_FILL('TABLE', 'HR', 'EMPLOYEES_COPY');
SELECT * FROM DEPTREE ORDER BY SEQ#;
CREATE OR REPLACE PROCEDURE EMPLOYEES_COPY_PROC_IN_1 IS BEGIN EMPLOYEES_COPY_PROC_IN; END; /
EXEC DEPTREE_FILL('TABLE', 'HR', 'EMPLOYEES_COPY');
SELECT * FROM DEPTREE ORDER BY SEQ#;
So in the above example, we can see that NESTED_LEVEL indicates that due to indirect dependency its value is increased. The view VW_EMPLOYEES_COPY_1 is directly dependent on table EMPLOYEES_COPY. So Oracle has to go one stage to view the dependency and due to this the NESTED_LEVEL is showing as 1. But for procedure EMPLOYEES_COPY_PROC_IN its value is showing as 2 as its directly dependent on view VW_EMPLOYEES_COPY_1 which in turn dependent on table EMPLOYEES_COPY.
List of Status of an object:
Every database object has one of the below status values.
| STATUS | DESCRIPTION |
|---|---|
| VALID | The object was successfully compiled using the current definition in the data dictionary. |
| COMPILED WITH ERRORS | The most recent attempt to compile the object produces error. |
| INVALID | An object is marked as invalid when any other object it references has changed. |
| UNAUTHORIZED | An access privilege on a dependent object has been revoked. |
INVALIDATION OF DEPENDENT OBJECT:
In the below example we can see that the procedure A is directly dependent on view V, while the view V is directly dependent on table T. So there is indirect dependency between procedure A and table T.
Now let us suppose we have changed the table structure. So the direct dependent object view V will be invalidated which in turn invalidated the procedure A.
So we can conclude that direct dependent object will be invalidated if the reference object is changed. Similarly the indirect dependent object can be invalidated (procedure A) by the changes to reference object (table T) that do not affect them.
RELATED TOPICS:
