CODEWITHSUNDEEP
oracle-databaseobjectschema
Will
Will

Reputation: 2978

Oracle views user

Now that I've learned about the concept of schema objects in Oracle and its order when matching the tables names.

For a sql query issued by user XYZ and accessing FOO_TABLE the order of preference would be to check

  1. User table: XYZ.FOO_TABLE
  2. User synonym: XYZ.FOO_TABLE
  3. Public synonym: PUBLIC.FOO_TABLE

Looking at the following data of a view in XML:

<ROW>
    <OWNER>XYZ</OWNER>
    <VIEW_NAME>BAR_VIEW</VIEW_NAME>
    <TEXT_LENGTH>...</TEXT_LENGTH>
    <TEXT>
        SELECT *
        FROM SOME_NAME
    </TEXT>
    <EDITIONING_VIEW>N</EDITIONING_VIEW>
    <READ_ONLY>N</READ_ONLY>
</ROW>

If I want to find the owner of SOME_TABLE, can I just start looking at the following order?

  1. User table: XYZ.SOME_NAME
  2. User synonym: XYZ.SOME_NAME
  3. Public synonym: PUBLIC.SOME_NAME

Best, Will

Upvotes: 2

Views: 247

Answers (1)

Allan
Allan

Reputation: 17429

You're statement can actually be simplified somewhat, to:

  1. User object: XYZ.SOME_TABLE
  2. Public synonym: PUBLIC.SOME_TABLE

This rule applies to all database object (tables, packages, etc.). The local synonym step isn't required because 1) it is a local object and 2) Oracle doesn't allow for a name conflict between local objects (i.e. you can't have a synonym and a table in one schema with the same name).

The only qualifier here is that if the view definition contains the schema (SELECT * FROM SOME_SCHEMA.SOME_TABLE) or a database link (SELECT * FROM SOME_TABLE@SOME_DB_LINK) then the name resolution is done from the indicated schema (in the case of a DB link, from the schema indicated in the DB link's definition).

Upvotes: 2

Related Questions