8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Enhanced Finer Grained Dependency Management in Oracle Database 11g Release 1
In previous releases, object dependencies were managed at the object level, so altering an object automatically invalidated all dependent objects. Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation. To show this create a table with a dependent package, which is in turn used by a view.
CREATE TABLE dep_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT dep_tab_pk PRIMARY KEY (id)
);
CREATE OR REPLACE PACKAGE dep_api AS
FUNCTION get_desc (p_id IN dep_tab.id%TYPE)
RETURN dep_tab.description%TYPE;
END dep_api;
/
CREATE OR REPLACE PACKAGE BODY dep_api AS
FUNCTION get_desc (p_id IN dep_tab.id%TYPE)
RETURN dep_tab.description%TYPE
AS
l_description dep_tab.description%TYPE;
BEGIN
SELECT description
INTO l_description
FROM dep_tab
WHERE id = p_id;
RETURN l_description;
END get_desc;
END dep_api;
/
CREATE OR REPLACE VIEW dept_tab_v AS
SELECT id, dep_api.get_desc(id) AS description
FROM dep_tab;
The objects in the schema are valid.
COLUMN object_name FORMAT A30 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- DEP_TAB TABLE VALID DEP_TAB_PK INDEX VALID DEP_API PACKAGE VALID DEP_API PACKAGE BODY VALID DEPT_TAB_V VIEW VALID 5 rows selected. SQL>
Add a column to the table and check the status of the schema objects. Prior to 11g we would expect both the package and the view to be invalidated by this operation.
ALTER TABLE dep_tab ADD ( record_type NUMBER(1) ); SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- DEP_TAB TABLE VALID DEP_TAB_PK INDEX VALID DEP_API PACKAGE VALID DEP_API PACKAGE BODY INVALID DEPT_TAB_V VIEW VALID 5 rows selected. SQL>
Notice the package specification and the view are not invalidated, because the table columns they reference are not changed by the addition of the new column. The package body is invalidated, but this is of little consequence since dependent objects reference the package specification, not the package body.
Next, recreate the package specification, adding the prototype for a new procedure, then check the status of the schema objects. We would expect this operation to invalidate the dependent view in releases prior to 11g.
CREATE OR REPLACE PACKAGE dep_api AS
FUNCTION get_desc (p_id IN dep_tab.id%TYPE)
RETURN dep_tab.description%TYPE;
PROCEDURE stub;
END dep_api;
/
SELECT object_name, object_type, status FROM user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
DEP_TAB TABLE VALID
DEP_TAB_PK INDEX VALID
DEP_API PACKAGE VALID
DEP_API PACKAGE BODY INVALID
DEPT_TAB_V VIEW VALID
5 rows selected.
SQL>
The dependent view is unaffected by the modification to the package specification.
For more information see:
Hope this helps. Regards Tim...