Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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...

Back to the Top.