When to qualify the schema name of an object in Oracle

Question

What determines whether an Oracle object (table, view, etc.) is required to be qualified with a schema name (for example, schema.table_name, or schema.view_name, etc.)? At times I am able to access a remote objects (via a DB link) without having to qualify the schema, but other times, I receive an error stating that the "table or view doesn't exist", and to correct this, I must qualify the schema name.

I am aware that it is a best practice to always qualify a schema name, but I am just curious why I am able to access certain remote objects without a qualified schema, and others only with a qualified schema.

Answer 1

From the Oracle documentation: http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements009.htm

The following example illustrates how Oracle resolves references to objects within SQL statements. Consider this statement that adds a row of data to a table identified by the name departments:

INSERT INTO departments 
VALUES 
(
 280, 
 'ENTERTAINMENT_CLERK', 
 206, 
 1700);

Based on the context of the statement, Oracle determines that departments can be:

• A table in your own schema
• A view in your own schema
• A private synonym for a table or view
• A public synonym

Oracle always attempts to resolve an object reference within the namespaces in your own schema before considering namespaces outside your schema. In this example, Oracle attempts to resolve the name departments as follows:

1. First, Oracle attempts to locate the object in the namespace in your own schema containing tables, views, and private synonyms. If the object is a private synonym, then Oracle locates the object for which the synonym stands. This object could be in your own schema, another schema, or on another database. The object could also be another synonym, in which case Oracle locates the object for which this synonym stands.

2. If the object is in the namespace, then Oracle attempts to perform the statement on the object. In this example, Oracle attempts to add the row of data to departments. If the object is not of the correct type for the statement, then Oracle returns an error. In this example, departments must be a table, view, or a private synonym resolving to a table or view. If departments is a sequence, then Oracle returns an error.

3. If the object is not in any namespace searched in thus far, then Oracle searches the namespace containing public synonyms. If the object is in that namespace, then Oracle attempts to perform the statement on it. If the object is not of the correct type for the statement, then Oracle returns an error. In this example, if departments is a public synonym for a sequence, then Oracle returns an error.

What it's saying is that Oracle will check locally for objects you call before expanding its search outwards. It may well be that there are public (or your own private) synonyms on some of your remote objects allowing you to reference them directly whereas those without the synonyms you'll have to fully qualify.