| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> LONG: Re: Order of table_name resolution
An object name takes the following form:
[schema.]name[@database]
Some examples include:
Emp_tab
Scott.Emp_tab
Scott.Emp_tab_at_Personnel
A session is established when a user logs onto a database. Object names are resolved relative to the current user session. The username of the current user is the default schema. The database to which the user has directly logged-on is the default database.
Oracle has separate namespaces for different classes of objects. All objects in the same namespace must have distinct names, but two objects in different namespaces can have the same name. Tables, views, snapshots, sequences, synonyms, procedures, functions, and packages are in a single namespace. Triggers, indexes, and clusters each have their own individual namespace. For example, there can be a table, trigger, and index all named SCOTT.EMP_TAB.
Based on the context of an object name, Oracle searches the appropriate namespace when resolving the name to an object. For example, in the following statement:
DROP CLUSTER Test
Oracle looks up TEST in the cluster namespace.
Rather than supplying an object name directly, you can also refer to an object using a synonym. A private synonym name has the same syntax as an ordinary object name. A public synonym is implicitly in the PUBLIC schema, but users cannot explicitly qualify a synonym with the schema PUBLIC.
Synonyms can only be used to reference objects in the same namespace as tables. Due to the possibility of synonyms, the following rules are used to resolve a name in a context that requires an object in the table namespace:
1.Look up the name in the table namespace.
2.If the name resolves to an object that is not a synonym, then no further work is necessary.
3.If the name resolves to a private synonym, then replace the name with the definition of the synonym and return to step 1.
4.If the name was originally qualified with a schema, then return an error; otherwise, check if the name is a public synonym.
5.If the name is not a public synonym, return an error; otherwise, then replace the name with the definition of the public
synonym and return to step 1.
When global object names are used in a distributed database (either explicitly or indirectly within a synonym), the local Oracle session resolves the reference as is locally required (for example, resolving a synonym to a remote table's global object name). After the partially resolved statement is shipped to the remote database, the remote Oracle session completes the resolution of the object as above.
Joe
>From: "Cale, Rick T (Richard)" <RICHARD.T.CALE_at_saic.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Order of table_name resolution
>Date: Wed, 14 Mar 2001 05:00:21 -0800
>MIME-Version: 1.0
>Received: from localhost (localhost [127.0.0.1])by parents.the-testas.net
>(8.9.3/8.8.7) with ESMTP id JAA11391for <jtestamail_at_localhost>; Wed, 14 Mar
>2001 09:10:40 -0500
>Received: from mail.the-testas.netby localhost with POP3
>(fetchmail-5.3.1)for jtestamail_at_localhost (multi-drop); Wed, 14 Mar 2001
>09:10:40 -0500 (EST)
>Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.192.199])by
>mail.acfi.net (8.11.0/8.11.0) with ESMTP id f2EF8Jw24812for
><oracle-l_at_the-testas.net>; Wed, 14 Mar 2001 09:08:19 -0600
>Received: from fatcity.UUCP (uucp_at_localhost)by newsfeed.cts.com
>(8.9.3/8.9.3) with UUCP id GAA80703;Wed, 14 Mar 2001 06:04:35 -0800 (PST)
>Received: by fatcity.com (26-Feb-2001/v1.0g-b70/bab) via UUCP id 002CC15E;
>Wed, 14 Mar 2001 05:00:21 -0800
>Return-Path: <root_at_fatcity.cts.com>
>Message-ID: <F001.002CC15E.20010314050021_at_fatcity.com>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "Cale, Rick T (Richard)" <RICHARD.T.CALE_at_saic.com>
>Sender: root_at_fatcity.com
>Errors-To: ML-ERRORS_at_fatcity.com
>Organization: Fat City Network Services, San Diego, California
>X-ListServer: v1.0g, build 70; ListGuru (c) 1996-2001 Bruce A. Bergman
>Precedence: bulk
>X-Fetchmail-Warning: recipient address ORACLE-L_at_fatcity.com didn't match
>any local name
>X-SpamBouncer: 1.2 (10/20/00)
>X-SBPass: NoBounce
>X-SBClass: OK
>Status:
>
>Hi DBAs,
>
>Can someone clarify/confirm on how Oracle determines what table to use in
>select statement?
>
>For ex. If I am login as Scott and do the following:
>
>SELECT * FROM employee;
>
>Of course Oracle will use Scott.employee if it exist. If it does not exist
>but exist under another schema. Does
>Oracle look at private synonyms then public synonyms or vice versa.
>
>My guess is Oracle checks in this order. Please confirm or correct.
>
>1. Check if table exist in current schema
>2. Check private synonyms
>3. Check public synonyms
>4. 3 strikes your out with an error.
>
>Rick Cale, Science Applications International Corp.
>Phone:865-481-2198, fax:865-481-8555
>e-mail: caler_at_saic.com
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Cale, Rick T (Richard)
> INET: RICHARD.T.CALE_at_saic.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph Testa INET: teci_dba1_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Mar 14 2001 - 10:40:33 CST
 |
 |