invalid, depending on the following condition: • 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, 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
table query, because the defining query is expanded at view creation
time and permanently stored in the data dictionary.
Oracle Dependency Management 18-5
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 by using packages.
Session State and Referenced Packages
Each session that references a package construct 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 (specification or body) are subsequently invalidated and recompiled.
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.
Additional Information: For information on forcing the recompilation of an invalid view or program unit, see the Oracle8 Server Application Developer’s Guide. If you are using Trusted Oracle, also see the Trusted Oracle Server Administrator’s Guide.
18-6 Oracle8 Server Concepts
Dependency Management and Nonexistent 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 nonexistence 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 the PUBLIC
role.
•
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 nonexistence of JWARD.EMP and on the existence of
PUBLIC.EMP.