Object names referenced in SQL statements can consist of several pieces, separated by periods. The following describes how the database resolves an object name.
Oracle Database attempts to qualify the first piece of the name referenced in the SQL statement. For example, in
scott is the first piece. If there is only one piece, the one piece is considered the first piece.
In the current schema, the database searches for an object whose name matches the first piece of the object name. If it does not find such an object, it continues with step b.
The database searches for a public synonym that matches the first piece of the name. If it does not find one, it continues with step c.
The database searches for a schema whose name matches the first piece of the object name. If it finds one, 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 the previously qualified schema or there is not a second piece, the database returns an error.
If no schema is found in step c, the object cannot be qualified and the database returns an error.
A schema object has been qualified. Any remaining pieces of the name must match a valid part of the found object. For example, if
scott.emp.deptno is the name,
scott is qualified as a schema,
emp is qualified as a table, and
deptno must correspond to a column (because
emp is a table). If
emp is qualified as a package,
deptno must correspond to a public constant, variable, procedure, or function of that package.
When global object names are used in a distributed database, either explicitly or indirectly within a synonym, the local database resolves the reference locally. For example, it resolves a synonym to global object name of a remote table. The partially resolved statement is shipped to the remote database, and the remote database completes the resolution of the object as described here.
Because of how the database resolves 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. For example, assume the following:
At the current point in time, the
company schema contains a table named
PUBLIC synonym named
emp is created for
company.emp and the
SELECT privilege for
company.emp is granted to the
jward schema does not contain a table or private synonym named
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;
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, the database notes that
jward.dept_salaries depends on the nonexistence of
jward.emp and on the existence of
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;
jward.emp does not have the same structure as
As it attempts to resolve references in object definitions, the database internally makes note of dependencies that the new dependent object has on "nonexistent" objects--schema objects that, if they existed, would change the interpretation of the object's definition. Such dependencies must be noted in case a nonexistent object is later created. If a nonexistent object is created, all dependent objects must be invalidated so that dependent objects can be recompiled and verified and all dependent function-based indexes must be marked unusable.
Therefore, in the previous example, as
jward.emp is created,
jward.dept_salaries is invalidated because it depends on
jward.emp. Then when
jward.dept_salaries is used, the database attempts to recompile the view. As the database resolves the reference to
emp, it finds
public.emp is no longer the referenced object). Because
jward.emp does not have a
sal column, the database finds errors when replacing the view, leaving it invalid.
In summary, you must manage dependencies on nonexistent objects checked during object resolution in case the nonexistent object is later created.
See Also:"Schema Objects and Database Links" for information about name resolution in a distributed database