| Oracle® D
atabase Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
| Oracle® D
atabase Concepts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
The definitions of some objects, including views and pro cedures, reference other objects, such as tables. As a result, the objects being defined are dependent on the objects referenced in t heir definitions. This chapter discusses the dependencies among schema objects and how Oracle automatically tracks and manages these dependencies.
This chapter contains the following topics:
Some types of schema objects can reference other objects as part of their definition. For example, a view i s defined by a query that references tables or other views. A procedure's body can include SQL statements that reference other object s 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 6-1 illustrates the different types of dependent and referenced objects:
Figure 6-1 Types of Possible Dependen t and Referenced Schema Objects
If you alter the definition of a referenced object, dependent objects may or may not continue to functio n without error, depending on the type of alteration. For example, if you drop a table, no view based on the dropped table is usable.
Oracle automatically records dependencies among objects to alleviate the complex job of dependency management for the databas e administrator and users. For example, if you alter a table on which several stored procedures depend, Oracle automatically recompil es the dependent procedures the next time the procedures are referenced (run or compiled against).
To manage dependencies amon g schema objects, all of the schema objects in a database have a status.
Valid schema objects have been compiled and can be immediately used when referenced.
Invalid schema objects must be compiled before the y can be used.
For procedures, functions, and packages, this means compiling the schema object.
For views, this means that the view must be reparsed, using the current definition in the data dictionary.< /p>
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 errors relating to the object occurred. 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. Whe n compiling a package, there might be a PL/SQL or SQL syntax error, or the correct privileges for a referenced object might not be pr esent. Schema objects with such problems remain invalid.
Oracle automatical ly tracks specific changes in the database and records the appropriate status for related objects in the data dictionary.
Stat us recording is a recursive process. Any change in the status of a referenced object changes the status not only for directly depende nt objects, but also for indirectly dependent objects.
For example, consider a stored procedure that directly references a vie w. In effect, the stored procedure indirectly references the base tables of that view. Therefore, if you alter a base table, the view is invalidated, which then invalidates the stored procedure. Figure 6-2 illustrates indirect dependencies:
When a schema object is reference d directly in a SQL statement or indirectly through a reference to a dependent object, Oracle checks the status of the object explici tly specified in the SQL statement and any referenced objects, as necessary. Oracle's action depends on the status of the objects tha t are directly and indirectly referenced in a SQL statement:
If every referenced object is valid, then O racle runs the SQL statement immediately without any additional work.
If any referenced view or PL/SQL program unit (procedure, function, or package) is invalid, then Oracle automatically attempts to compile the object.
If all invalid referenced objects can be compiled successfully, then they are co mpiled and Oracle runs the SQL statement.
If an invalid object cannot be compiled successfully, then it remains invalid. Oracle returns an error and rolls back the failing SQL statement. The rest of the transaction is unaltered and can b e committed or rolled back by the user.
|
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. |
A view or PL/SQL program unit can be compiled and made valid if the fo llowing conditions are satisfied:
The definition of the view or program unit must be correct. All of the SQL and PL/SQL statements must be proper constructs.
All referenced objects must be 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 must have the necessary privileges for the referenced objects. For ex
ample, if a SQL statement in a procedure inserts a row into a table, the owner of the procedure must have the INSERT pri
vilege for the referenced table.
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, then the defining query is expanded when stored in the data dic
tionary to include all columns in the referenced base table at that time.
If a base table or view of a view is altered, rename d, or dropped, then the view is invalidated, but its definition remains in the data dictionary along with the privileges, synonyms, o ther objects, and other views that reference the invalid view.
|
Note: Whenever you create a table, index, and view, and then drop the table, all objects depend ent on that table are invalidated, including views, packages, package bodies, functions, and procedures. |
An attempt to use an invalid view automatically causes Oracle to recompile the view d ynamically. After replacing the view, the view might be valid or invalid, depending on the following conditions:
All base tables referenced by the defining query of a view must exist. 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, then most depe ndent views can be recompiled successfully.
If a base table of a view is altered or re-created with at l
east the same set of columns, then the view can be validated. The view cannot be validated if the base table is re-created with new c
olumns and the view references columns no longer contained in the re-created table. The latter point is especially relevant in the ca
se of views defined with a SELECT * FROM table query, because the defining query is expanded at vi
ew creation time and permanently stored in the data dictionary.
Oracle automatically invalidates a program unit when the definition of a referenced o bject 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 t he referenced table is altered, then the dependent procedure is invalidated.
If the base table of the re ferenced view is altered, then the view and the dependent procedure are invalidated.
If the referenced s tandalone procedure is replaced, then the dependent procedure is invalidated.
If the body of th e referenced package is replaced, then the dependent procedure is not affected. However, if the specification of the referenced package is replaced, then the dependent procedure is invalidated. This is a mechanism for minimizing dependencies among p rocedures and referenced objects by using packages.
Whenever you create a table, index, and view, and th en drop the table, all objects dependent on that table are invalidated, including views, packages, package bodies, functions, and pro cedures.
So me data warehouses drop indexes on tables at night to facilitate faster loads. However, all views dependent on the table whose index is dropped get invalidated. This means that subsequently running any package that reference these dropped views will invalidate the p ackage.
Remember that whenever you create a table, index, and view, and then drop the index, all objects dependent on that tab le are invalidated, including views, packages, package bodies, functions, and procedures. This protects updatable join views.
To make the view valid again, use one of the following statements:
SELECT * FROM vtest;
or< /p>
ALTER VIEW vtest compile;
Each session that references a package construc t has its own instance of that 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 are subsequently inv alidated and recompiled.
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 inv
alidates the dependent objects to verify that an owner of a dependent object continues to have the necessary privileges for all refer
enced objects. Internally, Oracle notes that such objects do not have to be recompiled. Only security authorizations need to be valid
ated, not the structure of any objects. This optimization eliminates unnecessary recompilations and prevents the need to change a dep
endent object's time stamp.
|
See Also: Oracle Database Application Developer's Guide - Fundamentals for information about forcing the recompilation of an invalid view or program unit |
Object names referenced in SQL statements can consist of several pieces, separated by periods. The following describes how Oracle resolves an object name.
Oracle attempts to qualify the first piece of the name referenced in the SQL statement. For example,
in hr.employees, hr is the first piece. If there is only one piece, then the one piece is cons
idered the first piece.
In the current schema, Oracle searches for an object whose name matches the first p iece of the object name. If it does not find such an object, then it continues with step b.
Oracle searches for a pu blic synonym that matches the first piece of the name. If it does not find one, then it continues with step c.
Oracl e searches for a schema whose name matches the first piece of the object name. If it finds one, then it returns to step b, now using the second piece of the name as the object to find in the qualified schema. If the second piece does not correspond to an object in t he previously qualified schema or there is not a second piece, then Oracle returns an error.
If no schema is found i n step c, then the object cannot be qualified and Oracle returns an error.
A schema object has been qualified. Any r
emaining pieces of the name must match a valid part of the found object. For example, if hr.employees.department_id is the name, then hr is qualified as a schema, employees is qualified as a table, and
department_id must correspond to a column (because employees is a table). If employees is qua
lified as a package, then department_id must correspond to a public constant, variable, procedure, or function of that p
ackage.
Because of how Oracle reso lves references, it is possible for an object to depend on the nonexistence of other objects. This situation occurs when the dependent object uses a reference that would be interpreted differently were another object present.
In addition to managing dependencies among schema objects, Oracle also manages 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 specifica tion is recompiled, all dependent shared SQL areas are invalidated. At a subsequent execution of the cursor that corresponds to an in validated shared SQL area, Oracle reparses the SQL statement to regenerate the shared SQL area.
Tracking dependencies and completing necessary recompilations are perform ed automatically by Oracle. Local dependency management occurs when Oracle manages dependencies among the objects in a single database. For example, a statement in a procedure can reference a table in the same database.
Remote depende ncy management occurs when Oracle manages dependencies in distributed environments across a network. 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.
Oracle manages all local dependenc ies using the database's internal dependency table, which keeps track of each schema object's dependent objects. When a referenced ob ject is modified, Oracle uses the depends-on table to identify dependent objects, which are then invalidated.
For example, ass
ume a stored procedure UPDATE_SAL references the table JWARD.employees. If the definition of t
he table is altered in any way, the status of every object that references JWARD.employees is changed to UPDATE_SAL. As a result, the procedure cannot be run until it has bee
n recompiled and is valid. Similarly, when a DML privilege is revoked from a user, every dependent object in the user's schema is inv
alidated. However, an object that is invalid because authorization was revoked can be revalidated by "reauthorization," in which case
it does not require full recompilation.
Oracle also manages applicati on-to-database and distributed database dependencies. For example, an Oracle Forms application might contain a trigger that reference s a table, or a local stored procedure might call a remote procedure in a distributed database system. The database system must accou nt for dependencies among such objects. Oracle uses different mechanisms to manage remote dependencies, depending on the objects invo lved.
Dependencies among stored procedures including functions, packages, and triggers in a distributed database syste m are managed using time stamp checking or signature checking.
The dynamic initialization parameter REMOTE_DEPENDENCIES_MODE deter
mines whether time stamps or signatures govern remote dependencies.
See Also:
Oracl e Database Application Developer's Guide - Fundamentals for details about managing remote dependencies with time stamps or s ignaturesIn the time stamp ch ecking dependency model, whenever a procedure is compiled or recompiled its time stamp (the time it is created, alte red, or replaced) is recorded in the data dictionary. The time stamp is a record of the time the procedure is created, altered, or re placed. Additionally, the compiled version of the procedure contains information about each remote procedure that it references, incl uding the remote procedure's schema, package name, procedure name, and time stamp.
When a dependent procedure is used, Oracle compares the remote time stamps recorded at compile time with the current time stamps of the remotely referenced procedures. Dependin g on the result of this comparison, two situations can occur:
The local and remote procedures run withou t compilation if the time stamps match.
The local procedure is invalidated if any time stamps of remotel y referenced procedures do not match, and an error is returned to the calling environment. Furthermore, all other local procedures th at depend on the remote procedure with the new time stamp are also invalidated. For example, assume several local procedures call a r emote procedure, and the remote procedure is recompiled. When one of the local procedures is run and notices the different time stamp of the remote procedure, every local procedure that depends on the remote procedure is invalidated.
Actual time sta mp comparison occurs when a statement in the body of a local procedure runs a remote procedure. Only at this moment are the time stam ps compared using the distributed database's communications link. Therefore, all statements in a local procedure that precede an inva lid procedure call might run successfully. Statements subsequent to an invalid procedure call do not run at all. Compilation is requi red.
Depending on how the invalid procedure is called, DML statements run before the invalid procedure call are rolled back. F
or example, in the following, the UPDATE results are rolled back as the complete PL/SQL block changes are rolled back.
BEGIN
UPDATE table set ... invalid_proc; COMMIT;
END;
However, with the following, the UPDATE results are final. Only the PROC call
is rolled back.
UPDATE table set ... EXECUTE invalid_proc; COMMIT;
If the signature depend ency model is in effect, a dependency on a remote program unit causes an invalidation of the dependent unit if the dependent unit con tains a call to a procedure in the parent unit, and the signature of this procedure has been changed in an incompatible manner. A pro gram unit can be a package, stored procedure, stored function, or trigger.
Oracle does not manage dependencie s among remote schema objects other than local-procedure-to-remote-procedure dependencies.
For example, assume that a local vi ew 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 ar e 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 that errors are not returned. In such cases, lack o f dependency management is preferable to unnecessary recompilations of dependent objects.
Code in database applications can reference objects in the connected database. For example, OCI and precompiler applic ations can submit anonymous PL/SQL blocks. Triggers in Oracle Forms applications can reference a schema object.
Such applicati ons are dependent on the schema objects they reference. Dependency management techniques vary, depending on the development environme nt.
|
See Also: Manua ls for your application development tools and your operating system for more information about managing the remote dependencies withi n database applications |
|
 Copyright © 1993, 2003 Oracle Corporation All Rights Reserved. |
|
