is the name of the object.
schema
is the schema containing the object. The schema qualifier allows you to refer to an object in a schema other than your own. Note that you must be granted privileges to refer to objects in other schemas. If you omit this qualifier, Oracle7 assumes that you are referring to an object in your own schema.
Only schema objects can be qualified with schema. Schema objects are shown in Figure 2 - 1. Other objects, shown in Figure 2 - 2, cannot be qualified with schema because they are not schema objects, except for public synonyms which can optionally be qualified with "PUBLIC" (quotation marks required).
part
is a part of the object. This identifier allows you to refer to a part of a schema object, such as a column of a table. Note that not all types of objects have parts.
dblink
applies only to those using Oracle7 with the distributed option. This is the name of the database containing the object. The dblink qualifier allows you to refer to an object in a database other than your local database. If you omit this qualifier, Oracle7 assumes that you are referring to an object in your local database. Note that not all SQL statements allow you to access objects on remote databases.
You can include spaces around the periods separating the components of the reference to the object, but it is conventional to omit them.
How Oracle7 Resolves Object References
When you refer to an object in a SQL statement, Oracle7 considers the context of the SQL statement and locates the object in the appropriate namespace. If the named object cannot be found in the appropriate namespace, Oracle7 returns an error message. After locating the object, Oracle7 performs the statement's operation on the object.
The following example illustrates how Oracle7 resolves references to objects within SQL statements. Consider this statement that adds a row of data to a table identified by the name DEPT:
INSERT INTO dept
VALUES (50, 'SUPPORT', 'PARIS')
Based on the context of the statement, Oracle7 determines that DEPT can be:
- a table in your own schema
- a view in your own schema
- a private synonym for a table or view
Oracle7 always attempts to resolve an object reference within the namespaces in your own schema before considering namespaces outside your schema. In this example, Oracle7 attempts to resolve the name DEPT in these ways:
1. Oracle7 first attempts to locate the object in the namespace in your own schema containing tables, views, and private synonyms (see Figure 2 - 1). If the object is a private synonym, Oracle7 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 Oracle7 locates the object for which this synonym stands.
If the object is in the namespace, Oracle7 attempts to perform the statement on the object. In this example, Oracle7 attempts to add the row of data to DEPT. If the object is not of the correct type for the statement, Oracle7 returns an error message. In this example, DEPT must be a table, view, or a private synonym resolving to a table or view. If DEPT is a sequence, Oracle7 returns an error message.
2. If the object is not in the namespace searched in Step 1, Oracle7 searches the namespace containing public synonyms (see Figure 2 - 2). If the object is in the namespace, Oracle7 attempts to perform the statement on it. If the object is not of the correct type for the statement, Oracle7 returns an error message. In this example, if DEPT is a public synonym for a sequence, Oracle7 returns an error message.
Referring to Objects in Other Schemas
To refer to objects in schemas other than your own, prefix the object name with the schema name:
schema.object
For example, this statement drops the EMP table in the schema SCOTT:
DROP TABLE scott.emp
Referring to Objects in Remote Databases
To refer to objects in databases other than your local database, follow the object name with the name of the database link to that database. A database link is a schema object that causes Oracle7 to connect to a remote database to access an object there. This section tells you:
- how to create database links
- how to use database links in your SQL statements
Creating Database Links
You can create a database link with the CREATE DATABASE LINK command described![[*]](jump.gif)
, "Commands," of this manual. The command allows you to specify this information about the database link:
- the name of the database link
- the connect string to access the remote database
- the username and password to connect to the remote database
Oracle7 stores this information in the data dictionary.
Names When you create a database link, you must specify its name. The name of a database link can be as long as 128 bytes and can contain periods (.) and the special character @. In these ways, database link names are different from names of other types of objects.
The name that you give to a database link must correspond to the name of the database to which the database link refers and the location of that database in the hierarchy of database names. The following syntax diagram shows the form of the name of a database link:
where:
database specifies the name of the remote database to which the database link connects. The name of the remote database is specified by its initialization parameter DB_NAME.
domain
specifies the domain of the remote database to which the database link connects. If you omit the domains from the name of a database link, Oracle7 expands the name by qualifying database with the domain of your local database before storing it in the data dictionary. The domain of a database is specified by the value of its initialization parameter DB_DOMAIN.
connection_qualifier
allows you to further qualify a database link. Using connection qualifiers, you can create multiple database links to the same database. For example, you can use connection qualifiers to create multiple database links to different instances of the Oracle7 Parallel Server that access the same database.
Username and Password The username and password are used by Oracle7 to connect to the remote database. The username and password for a database link are optional.
Database String The database string is the specification used by SQL*Net to access the remote database. For information on writing database connect strings, see the SQL*Net documentation for your specific network protocol. The database string for a database link is optional.
Referring to Database Links
Database links are available only to those using Oracle7 with the distributed option. When you issue a SQL statement that contains a database link, you can specify the database link name in one of these forms:
complete is the complete database link name as stored in the data dictionary including the database, domain, and optional connection_qualifier components.
partial
contains the database and optional connection_qualifier components, but not the domain component.
Oracle7 performs these tasks before connecting to the remote database:
1. If the database link name specified in the statement is partial, Oracle7 expands the name to contain the domain of the local database (specified by the initialization parameter DB_DOMAIN).
2. Oracle7 first searches for a private database link in your own schema with the same name as the database link in the statement, and then, if necessary, searches for a public database link with the same name.
- 2.1 Oracle7 always determines the username and password from the first matching database link (either private or public). If the first matching database link has an associated username and password, Oracle7 uses it. If it does not have an associated username and password, Oracle7 uses your current username and password.
- 2.2 If the first matching database link has an associated database string, Oracle7 uses it. If not, Oracle7 searches for the next matching (public) database link. If there is no matching database link, or if no matching link has an associated database string, Oracle7 returns an error message.
3. Oracle7 uses the database string to access the remote database. After accessing the remote database, Oracle7 verifies that both of these conditions are true:
- The name of the remote database (specified by its initialization parameter DB_NAME) must match the database component of the database link name.
- The domain (specified by the initialization parameter DB_DOMAIN) of the remote database must match the domain component of the database link name.
If both of these conditions are true, Oracle7 proceeds with the connection, using the username and password chosen in step 2a. If not, Oracle7 returns an error message.
4. If the connection using the database string, username, and password is successful, Oracle7 attempts to access the specified object on the remote database using the rules for resolving object references and referring to objects in other schemas presented earlier in this section.
You can enable and disable Oracle7 resolution of names for remote objects using the initialization parameter GLOBAL_NAMES and the GLOBAL_NAMES parameter of the ALTER SYSTEM and ALTER SESSION commands.
You cannot use the USERENV('TERMINAL') variable in the WHERE clauses of INSERT, UPDATE, or DELETE statements that access remote objects, although you can do so in SELECT statements.
For more information on remote name resolution, see the "Database Administration" chapter of Oracle7 Server Distributed Systems, Volume I.
where: