Dependencies Among Schema Objects
Whoever you are -- I have always depended on the kindness of strangers.
Tennessee Williams: A Streetcar Named Desire
The definitions of certain objects, such as views and procedures, reference other objects, such as tables. Therefore, some objects are dependent on the objects referenced in their definitions. This chapter discusses the dependencies among objects and how Oracle automatically tracks and manages such dependencies. It includes:
If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for more information on schema object dependencies in that environment.
An Introduction to Dependency Issues
Some types of schema objects can reference other objects as part of their definition. For example, a view is defined by a query that references tables or other views; a procedure's body can include SQL statements that reference other objects of a database. An object that references another object as part of its definition is called a dependent object, while the object being referenced is a referenced object. Figure 16 - 1 illustrates the different types of dependent and referenced objects.
Figure 16 - 1. Types of Possible Dependent and Referenced Schema Objects
If you alter the definition of a referenced object, dependent objects may, or may not, continue to function without error, depending on the type of alteration. For example, if you drop a table, no view based on the dropped table can be used.
Oracle automatically records dependencies among objects to alleviate the complex job of dependency management from the database administrator and users. For example, if you alter a table on which several stored procedures depend, Oracle automatically recompiles the dependent procedures the next time the procedures are referenced (executed or compiled against).
To manage dependencies among objects, all schema objects in a database have a status:
INVALID The object must be compiled before it can be used. In the case of procedures, functions, and packages, this means compiling the object. In the case of views, this means that the view must be reparsed, using the current definition in the data dictionary. Only dependent objects can be invalid; tables, sequences, and synonyms are always valid.
If a view, procedure, function, or package is invalid, Oracle may have attempted to compile it, but there were some errors relating to the object. For example, when compiling a view, one of its base tables might not exist, or the correct privileges for the base table might not be present. When compiling a package, there might be a PL/SQL or SQL syntax error, or the correct privileges for a referenced object might not be present. Objects with such problems remain invalid.
VALID
The object has been compiled and can be immediately used when referenced.
Oracle automatically tracks specific changes in the database and records the appropriate status for related objects in the data dictionary.
Status recording is a recursive process; any change in the status of a referenced object not only changes the status for directly dependent objects, but also for indirectly dependent objects. For example, consider a stored procedure that directly references a view. In effect, the stored procedure indirectly references the base table(s) of that view. Therefore, if you alter a base table, the view is invalidated, which then invalidates the stored procedure. Figure 16 - 2 illustrates this.
Figure 16 - 2. Indirect Dependencies
When an object is referenced (directly in a SQL statement or indirectly via a reference to a dependent object), Oracle checks the status of the object explicitly specified in the SQL statement and any referenced objects, as necessary. Depending on the status of the objects that are directly and indirectly referenced in a SQL statement, different events can occur.
- If every referenced object is valid, the SQL statement executes immediately without any additional work.
- If any referenced view or procedure (including functions and packages) is invalid, Oracle automatically attempts to compile the object. If all referenced objects that are invalid can be successfully compiled, the objects are compiled, and the SQL statement executes successfully. If an invalid object cannot be successfully compiled, the object remains invalid, an error is returned, and the transaction containing the SQL statement is rolled back.
Note: Oracle attempts to recompile an invalid object dynamically only if it has not been replaced since it was detected as invalid. This optimization eliminates unnecessary recompilations.
Compiling Views and PL/SQL Program Units
A view or PL/SQL program unit can be compiled and made valid if the following conditions are true:
- The definition of the view or program unit is correct; all SQL and PL/SQL statements must be proper constructs.
- All referenced objects are present and of the expected structure. For example, if the defining query of a view includes a column, the column must be present in the base table.
- The owner of the view or program unit has the necessary privileges for the referenced objects. For example, if a SQL statement in a procedure inserts a row into a table, the owner of the procedure must have the INSERT privilege for the referenced table.
Views and Base Tables
A view depends on the base tables (or views) referenced in its defining query. If the defining query of a view is not explicit about which columns are referenced, for example, SELECT * FROM table, the defining query is expanded when stored in the data dictionary to include all columns in the referenced base table at that time. If a base table (or view) of a view is altered, renamed, or dropped, the view is invalidated, but its definition remains in the data dictionary along with the privileges, synonyms, other objects, and other views that reference the invalid view.
Attempting to use an invalid view automatically causes Oracle to recompile the view dynamically. After replacing the view, the view might be valid or invalid, depending on the following:
- All base tables referenced by the defining query of a view must exist. Therefore, if a base table of a view is renamed or dropped, the view is invalidated and cannot be used. References to invalid views cause the referencing statement to fail. The view can be compiled only if the base table is renamed to its original name or the base table is re-created.
- If a base table is altered or re-created with the same columns, but the datatype of one or more columns in the base table is changed, any dependent view can be recompiled successfully.
- If a base table of a view is altered or re-created with at least the same set of columns, the view can be validated. The view cannot be validated if the base table is re-created with new columns and the view references columns no longer contained in the re-created table. The latter point is especially relevant in the case of views defined with a "SELECT * FROM . . ." query, because the defining query is expanded at view creation time and permanently stored in the data dictionary.
Program Units and Referenced Objects
Oracle automatically invalidates a program unit when the definition of a referenced object is altered. For example, assume that a standalone procedure includes several statements that reference a table, a view, another standalone procedure, and a public package procedure. In that case, the following conditions hold:
- If the referenced table is altered, the dependent procedure is invalidated.
- If the base table of the referenced view is altered, the view and the dependent procedure are invalidated.
- If the referenced standalone procedure is replaced, the dependent procedure is invalidated.
- If the body of the referenced package is replaced, the dependent procedure is not affected. However, if the specification of the referenced package is replaced, the dependent procedure is invalidated.
This last case reveals a mechanism for minimizing dependencies among procedures and referenced objects.
Security Authorizations
Oracle notices when a DML object or system privilege is granted to or revoked from a user or PUBLIC and automatically invalidates all the owner's dependent objects. Oracle invalidates the dependent objects to verify that an owner of a dependent object continues to have the necessary privileges for all referenced objects. Internally, Oracle notes that such objects do not have to be "recompiled"; only security authorizations need to be validated, not the structure of any objects. This optimization eliminates unnecessary recompilations and prevents the need to change a dependent object's timestamp.
Advanced Dependency Management Topics
The previous section described conceptually the dependency management mechanisms of Oracle. The following sections offer additional information about Oracle's automatic dependency management features. For information on forcing the recompilation of an invalid view or program unit, see the Oracle7 Server Application Developer's Guide. If you are using Trusted Oracle, also see the Trusted Oracle7 Server Administrator's Guide.
Dependency Management and Non-Existent Schema Objects
When a dependent object is created, Oracle attempts to resolve all references by first searching in the current schema. If a referenced object is not found in the current schema, Oracle attempts to resolve the reference by searching for a private synonym in the same schema. If a private synonym is not found, Oracle moves on, looking for a public synonym. If a public synonym is not found, Oracle searches for a schema name that matches the first portion of the object name. If a matching schema name is found, Oracle attempts to find the object in that schema. If no schema is found, an error is returned.
Because of how Oracle resolves references, it is possible for an object to depend on the non-existence of other objects. This occurs when the dependent object uses a reference that would be interpreted differently were another object present. For example, assume the following:
- At the current point in time, the COMPANY schema contains a table named EMP.
- A PUBLIC synonym named EMP is created for COMPANY.EMP and the SELECT privilege for COMPANY.EMP is granted to PUBLIC.
- The JWARD schema does not contain a table or private synonym named EMP.
- The user JWARD creates a view in his schema with the following statement:
CREATE VIEW dept_salaries AS
SELECT deptno, MIN(sal), AVG(sal), MAX(sal) FROM emp
GROUP BY deptno
ORDER BY deptno;
When JWARD creates the DEPT_SALARIES view, the reference to EMP is resolved by first looking for JWARD.EMP as a table, view, or private synonym, none of which is found, and then as a public synonym named EMP, which is found. As a result, Oracle notes that JWARD.DEPT_SALARIES depends on the non-existence of JWARD.EMP and on the existence of PUBLIC.EMP.
Now assume that JWARD decides to create a new view named EMP in his schema using the following statement:
CREATE VIEW emp AS
SELECT empno, ename, mgr, deptno
FROM company.emp;
Note: Notice that JWARD.EMP does not have the same structure as COMPANY.EMP.
As it attempts to resolve references in object definitions, Oracle internally makes note of dependencies that the new dependent object has on "non-existent" objects -- objects that, if they existed, would change the interpretation of the object's definition. Such dependencies must be noted in case a non-existent object is later created. If a non-existent object is created, all dependent objects must be invalidated so that dependent objects can be recompiled and verified.
Therefore, in the example above, as JWARD.EMP is created, JWARD.DEPT_SALARIES is invalidated because it depends on JWARD.EMP. Then when JWARD.DEPT_SALARIES is used, Oracle attempts to recompile the view. As Oracle resolves the reference to EMP, it finds JWARD.EMP (PUBLIC.EMP is no longer the referenced object). Because JWARD.EMP does not have a SAL column, Oracle finds errors when replacing the view, leaving it invalid.
In summary, dependencies on non-existent objects checked during object resolution must be managed in case the non-existent object is later created.
Shared SQL Dependency Management
In addition to managing the dependencies among schema objects, Oracle also manages the dependencies of each shared SQL area in the shared pool. If a table, view, synonym, or sequence is created, altered, or dropped, or a procedure or package specification is recompiled, all dependent shared SQL areas are invalidated. At a subsequent execution of the cursor that corresponds to an invalidated shared SQL area, Oracle reparses the SQL statement to regenerate the shared SQL area.
Package Invalidations and Session State
Each session that references a package construct has its own instance of the corresponding package, including a persistent state of any public and private variables, cursors, and constants. All of a session's package instantiations (including state) can be lost if any of the session's instantiated packages (specification or body) are subsequently invalidated and recompiled.
Local and Remote Dependency Management
Tracking dependencies and completing necessary recompilations are important tasks automatically performed by Oracle. In the simplest case, dependencies must be managed among the objects in a single database (local dependency management). For example, a statement in a procedure can reference a table in the same database. In more complex systems, Oracle must manage the dependencies in distributed environments across a network (remote dependency management). For example, an Oracle Forms trigger can depend on a schema object in the database. In a distributed database, a local view's defining query can reference a remote table.
Managing Local Dependencies
Oracle manages all local dependency checking using the database's internal "depends-on" table, which keeps track of each object's dependent objects. When a referenced object is modified, Oracle uses the depends-on table to identify dependent objects, which are then invalidated. For example, assume that there is a stored procedure UPDATE_SAL that references the table JWARD.EMP. If the definition of the table is altered in any way, the status of every object that references JWARD.EMP is changed to INVALID, including the stored procedure UPDATE_SAL. This implies that the procedure cannot be executed until the procedure has been recompiled and is valid. Similarly, when a DML privilege is revoked from a user, every dependent object in the user's schema is invalidated. However, an object that is invalid because authorization was revoked can be revalidated by "reauthorization", which incurs less overhead than a full recompilation.
Managing Remote Dependencies
Application-to-database and distributed database dependencies must also be considered. For example, an Oracle Forms application can contain a trigger that references a table, or a local stored procedure can call a remote procedure in a distributed database system. The database system must account for dependencies among such objects. Oracle manages remote dependencies using different mechanisms, depending on the objects involved.
Dependencies Among Local and Remote Database Procedures Dependencies among stored procedures (including functions, packages, and triggers) in a distributed database system are managed using timestamp checking. For example, when a procedure is compiled, such as during creation or subsequent replacement, its timestamp (the time it is created, altered, or replaced) is recorded in the data dictionary. Additionally, the compiled version of the procedure includes information (such as schema, package name, procedure name, and timestamp) for each remote procedure it references.
When a dependent procedure is used, Oracle compares the remote timestamps recorded at compile time with the current timestamps of the remotely referenced procedures. Depending on the result of this comparison, two situations can occur:
- The local and remote procedures execute without compilation if the timestamps match.
- The local procedure is invalidated if any timestamps of remotely referenced procedures do not match, and an error is returned to the calling environment. Furthermore, all other local procedures that depend on the remote procedure with the new timestamp are also invalidated. For example, assume several local procedures call a remote procedure, and the remote procedure is recompiled. When one of the local procedures is executed and notices the different timestamp of the remote procedure, every local procedure that depends on the remote procedure is invalidated.
Actual timestamp comparison occurs when a statement in the body of a local procedure executes a remote procedure; only at this moment are the timestamps compared via the distributed database's communications link. Therefore, all statements in a local procedure, previous to an invalid procedure call, might execute successfully, while statements subsequent to an invalid procedure call do not execute at all (compilation is required). However, any DML statements executed before the invalid procedure call are rolled back.
Dependencies Among Other Remote Schema Objects Dependencies among remote schema objects other than local procedure-to-remote procedure dependencies are not managed by Oracle.
For example, assume that a local view is created and defined by a query that references a remote table. Also assume that a local procedure includes a SQL statement that references the same remote table. Later, the definition of the table is altered.
As a result, the local view and procedure are never invalidated, even if the view or procedure is used after the table is altered, and even if the view or procedure now returns errors when used (in this case, the view or procedure must be altered manually so errors are not returned). Lack of dependency management is preferable in such cases to avoid unnecessary recompilations of dependent objects.
Dependencies of Applications Code in database applications can reference objects in the connected database; for example, OCI, Precompiler, and SQL*Module applications can submit anonymous PL/SQL blocks, and triggers in Oracle Forms applications can reference a schema object.
Such applications are dependent on the schema objects they reference. Dependency management techniques vary, depending on the development environment. Refer to the appropriate manuals for your application development tools and your operating system for more information about managing the remote dependencies within database applications.
