Geek Answers Handbook

When to Qualify an Object Schema Name in Oracle

What determines whether an Oracle object (table, view, etc.) needs to qualify with a schema name (for example, schema name .table_name or schema.view_name, etc.)? Sometimes I can access remote objects (via a DB link) without having to qualify the schema, but sometimes I get an error message indicating that "the table or view does not exist", and to fix this, I must match the name of the schema.

I know that it is always better to qualify a schema name, but I just wonder why I can access some remote objects without a qualified schema, and others only with a qualified schema.

+4
source share
2 answers

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

The following example shows how Oracle resolves object references in SQL operations. Consider this statement, which adds a row of data to a table identified by name departments:

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

Depending on the context of the instruction, Oracle determines that departments can be:

  • Table in your own schema
  • View in your own layout
  • Private synonym for table or view
  • Public synonym

Oracle always tries to resolve an object reference in namespaces in your own schema before looking at namespaces outside of your schema. In this example, Oracle tries to resolve name departments as follows:

  • First, Oracle is trying to find an object in the namespace in your own schema containing tables, views, and private synonyms. If the object is a private synonym, then Oracle finds the object for which the synonym stands. This object may be in your own schema, another schema, or in another database. An object can also be another synonym, in which case Oracle finds an object for which this synonym stands.

  • If the object is in the namespace, then Oracle tries to execute the object statement. In this example, Oracle is trying to add a row of data to departments. If the object does not match the correct type for the operator, then Oracle returns an error. In this example, departments should be a table, view, or private synonym that resolves a table or view. If the departments are a sequence, then Oracle returns an error.

  • If the object is not in any namespace that is still being looked at, Oracle looks for a namespace that contains public synonyms. If the object is in this namespace, then Oracle tries to execute the statement on it. If the object does not match the correct type for the operator, then Oracle returns an error. In this example, if departments are a public synonym for a sequence, then Oracle returns an error.

It is said that Oracle will locally inspect the objects you call before expanding its search to the outside. It is possible that some of your remote objects have public (or your own) synonyms that allow you to refer to them directly, while those who do not have synonyms, you will have to fully qualify.

+10
source

It depends on what username you used when logging in. Or what username was used to create / configure the database link.

See, for each scheme there is a user. If you are logged in as an "XYZ" user, you do not need to qualify the object in the "XYZ" schema.

0
source

More articles:


All Articles