A homogenous distributed database system is a network of two or more Oracle Databases that reside on one or more systems.

Figure 31-1 illustrates a distributed system that connects three databases: hq, mfg, and sales. An application can simultaneously access or modify the data in several databases in a single distributed environment. For example, a single query from a Manufacturing client on local database mfg can retrieve joined data from the products table on the local database and the dept table on the remote hq database.

For a client application, the location and platform of the databases are transparent. You can also create synonyms for remote objects in the distributed system so that users can access them with the same syntax as local objects. For example, if you are connected to database mfg but want to access data on database hq, creating a synonym on mfg for the remote dept table enables you to issue this query:

SELECT * FROM dept;

In this way, a distributed system gives the appearance of native data access. Users on mfg do not have to know that the data they access resides on remote databases.

Figure 31-1 Homogeneous Distributed Database

Description of "Figure 31-1 Homogeneous Distributed Database"

An Oracle Database distributed database system can incorporate Oracle Databases of different releases. All supported releases of Oracle Database can participate in a distributed database system. Nevertheless, the applications that work with the distributed database must understand the functionality that is available at each node in the system. A distributed database application cannot expect an Oracle7 database to understand the SQL extensions that are only available with Oracle Database.

The terms distributed database and distributed processing are closely related, yet have distinct meanings.

There definitions are as follows:

• Distributed database

  A set of databases in a distributed system that can appear to applications as a single data source.

• Distributed processing

  The operations that occurs when an application distributes its tasks among different computers in a network. For example, a database application typically distributes front-end presentation tasks to client computers and allows a back-end database server to manage shared access to a database. Consequently, a distributed database application processing system is more commonly referred to as a client/server database application system.

Distributed database systems employ a distributed processing architecture. For example, an Oracle Database server acts as a client when it requests data that another Oracle Database server manages.

The terms distributed database system and database replication are related, yet distinct.

In a pure (that is, not replicated) distributed database, the system manages a single copy of all data and supporting database objects. Typically, distributed database applications use distributed transactions to access both local and remote data and modify the global database in real-time.

The term replication refers to the operation of copying and maintaining database objects in multiple databases belonging to a distributed system. While replication relies on distributed database technology, database replication offers applications benefits that are not possible within a pure distributed database environment.

Most commonly, replication is used to improve local database performance and protect the availability of applications because alternate data access options exist. For example, an application may normally access a local database rather than a remote server to minimize network traffic and achieve maximum performance. Furthermore, the application can continue to function if the local server experiences a failure, but other servers with replicated data remain accessible.

In a heterogeneous distributed database system, at least one of the databases is a non-Oracle Database system. To the application, the heterogeneous distributed database system appears as a single, local, Oracle Database. The local Oracle Database server hides the distribution and heterogeneity of the data.

The Oracle Database server accesses the non-Oracle Database system using Oracle Heterogeneous Services with an agent. If you access the non-Oracle Database data store using an Oracle Transparent Gateway, then the agent is a system-specific application. For example, if you include a Sybase database in an Oracle Database distributed system, then you must obtain a Sybase-specific transparent gateway so that the Oracle Database in the system can communicate with it.

Alternatively, you can use generic connectivity to access non-Oracle Database data stores so long as the non-Oracle Database system supports the ODBC or OLE DB protocols.

Heterogeneous Services (HS) is an integrated component within the Oracle Database server and the enabling technology for the current suite of Oracle Transparent Gateway products.

HS provides the common architecture and administration mechanisms for Oracle Database gateway products and other heterogeneous access facilities. Also, it provides upwardly compatible functionality for users of most of the earlier Oracle Transparent Gateway releases.

For each non-Oracle Database system that you access, Heterogeneous Services can use a transparent gateway agent to interface with the specified non-Oracle Database system. The agent is specific to the non-Oracle Database system, so each type of system requires a different agent.

The transparent gateway agent facilitates communication between Oracle Database and non-Oracle Database systems and uses the Heterogeneous Services component in the Oracle Database server. The agent executes SQL and transactional requests at the non-Oracle Database system on behalf of the Oracle Database server.

Generic connectivity enables you to connect to non-Oracle Database data stores by using either a Heterogeneous Services ODBC agent or a Heterogeneous Services OLE DB agent.

Both are included with your Oracle product as a standard feature. Any data source compatible with the ODBC or OLE DB standards can be accessed using a generic connectivity agent.

The advantage to generic connectivity is that it may not be required for you to purchase and configure a separate system-specific agent. You use an ODBC or OLE DB driver that can interface with the agent. However, some data access features are only available with transparent gateway agents.

When creating the link, you determine which user should connect to the remote database to access the data.

The following table explains the differences among the categories of users involved in database links:

Connected user links have no connect string associated with them. The advantage of a connected user link is that a user referencing the link connects to the remote database as the same user, and credentials do not have to be stored in the link definition in the data dictionary.

Connected user links have some disadvantages. Because these links require users to have accounts and privileges on the remote databases to which they are attempting to connect, they require more privilege administration for administrators. Also, giving users more privileges than they need violates the fundamental security concept of least privilege: users should only be given the privileges they need to perform their jobs.

The ability to use a connected user database link depends on several factors, chief among them whether the user is authenticated by the database using a password, or externally authenticated by the operating system or a network authentication service. If the user is externally authenticated, then the ability to use a connected user link also depends on whether the remote database accepts remote authentication of users, which is set by the REMOTE_OS_AUTHENT initialization parameter.

The REMOTE_OS_AUTHENT parameter operates as follows:

If the connected user database link is accessed from within a definer's rights function, procedure, or package, then the definer's authorization ID is used to connect as a remote user. For example, if user jane calls procedure scott.p (a definer's rights procedure created by scott), and the link appears inside procedure scott.p, then scott is the connected user. To run a definer's rights function, procedure, or package that includes a connected user database link, the user who invokes the function, procedure, or package must be granted the INHERIT REMOTE PRIVILEGES privilege.

A benefit of a fixed user link is that it connects a user in a primary database to a remote database with the security context of the user specified in the connect string.

For example, local user joe can create a public database link in joe's schema that specifies the fixed user scott with password password. If jane uses the fixed user link in a query, then jane is the user on the local database, but she connects to the remote database as scott/password.

Fixed user links have a user name and password associated with the connect string. The user name and password are stored with other link information in data dictionary tables.

Current user database links make use of a global user. A global user must be authenticated by an X.509 certificate or a password, and be a user on both databases involved in the link.

The user invoking the CURRENT_USER link does not have to be a global user. For example, if jane is authenticated (not as a global user) by password to the Accounts Payable database, she can access a stored procedure to retrieve data from the hq database. The procedure uses a current user database link, which connects her to hq as global user scott. User scott is a global user and authenticated through a certificate over SSL, but jane is not.

Note that current user database links have these consequences:

• If the current user database link is not accessed from within a stored object, then the current user is the same as the connected user accessing the link. For example, if scott issues a SELECT statement through a current user link, then the current user is scott.

• When executing a stored object such as a procedure, view, or trigger that accesses a database link, the current user is the user that owns the stored object, and not the user that calls the object. For example, if jane calls procedure scott.p (created by scott), and a current user link appears within the called procedure, then scott is the current user of the link.

• If the stored object is an invoker's rights function, procedure, or package, then the invoker's authorization ID is used to connect as a remote user. For example, if user jane calls procedure scott.p (an invoker's rights procedure created by scott), and the link appears inside procedure scott.p, then jane is the current user.

• You cannot connect to a database as an enterprise user and then use a current user link in a stored procedure that exists in a shared, global schema. For example, if user jane accesses a stored procedure in the shared schema guest on database hq, she cannot use a current user link in this schema to log on to a remote database.

  See Also:

  • "Distributed Database Security" for more information about security issues relating to database links

  • Oracle Database PL/SQL Language Reference for more information about invoker's rights functions, procedures, or packages.

  • Oracle Database Security Guide for information about running a definer's rights function, procedure, or package that includes a current user database link

A global object name is an object specified using a database link.

The essential components of a global object name are:

• Object name

• Database name

• Domain

The following table shows the components of an explicitly specified global database object name:

Whenever a SQL statement includes a reference to a global object name, the database searches for a database link with a name that matches the database name specified in the global object name. For example, if you issue the following statement:

SELECT * FROM scott.emp@orders.us.example.com;

The database searches for a database link called orders.us.example.com. The database performs this operation to determine the path to the specified remote database.

The database always searches for matching database links in the following order:

1. Private database links in the schema of the user who issued the SQL statement.

2. Public database links in the local database.

3. Global database links (only if a directory server is available).

For SQL statements with a complete global database name, the database searches only for links that match the specified global database name.

Assume that you issue the following SQL statement, which specifies a complete global database name:

SELECT * FROM emp@prod1.us.example.com;

In this case, both the database name (prod1) and domain components (us.example.com) are specified, so the database searches for private, public, and global database links.

If any part of the domain is specified, then the database assumes that a complete global database name is specified.

If a SQL statement specifies a partial global database name (that is, only the database component is specified), the database appends the value in the DB_DOMAIN initialization parameter to the value in the DB_NAME initialization parameter to construct a complete name. For example, assume you issue the following statements:

CONNECT scott@locdb
SELECT * FROM scott.emp@orders;

If the network domain for locdb is us.example.com, then the database appends this domain to orders to construct the complete global database name of orders.us.example.com. The database searches for database links that match only the constructed global name. If a matching link is not found, the database returns an error and the SQL statement cannot execute.

If a global object name references an object in the local database and a database link name is not specified using the @ symbol, then the database automatically detects that the object is local and does not search for or use database links to resolve the object reference.

For example, assume that you issue the following statements:

CONNECT scott@locdb
SELECT * from scott.emp;

Because the second statement does not specify a global database name using a database link connect string, the database does not search for database links.

The database does not necessarily stop searching for matching database links when it finds the first match. The database must search for matching private, public, and network database links until it determines a complete path to the remote database (both a remote account and service name).

The first match determines the remote schema as illustrated in the following table:

After the database determines a complete path, it creates a remote session, assuming that an identical connection is not already open on behalf of the same local session. If a session already exists, the database reuses it.

After the local Oracle Database connects to the specified remote database on behalf of the local user that issued the SQL statement, object resolution continues as if the remote user had issued the associated SQL statement.

The first match determines the remote schema according to the following rules:

If the database cannot find the object, then it checks public objects of the remote database. If it cannot resolve the object, then the established remote session remains but the SQL statement cannot execute and returns an error.

The following are examples of global object name resolution in a distributed database system. For all the following examples, assume that:

An example illustrates how the database resolves a complete global object name and determines the appropriate path to the remote database using both a private and public database link.

For this example, assume the following:

• The remote database is named sales.division3.example.com.

• The local database is named hq.division3.example.com.

• A directory server (and therefore, global database links) is not available.

• A remote table emp is contained in the schema tsmith.

Consider the following statements issued by scott at the local database:

CONNECT scott@hq

CREATE PUBLIC DATABASE LINK sales.division3.example.com 
CONNECT TO guest IDENTIFIED BY network 
  USING 'dbstring'; 

Later, JWARD connects and issues the following statements:

CONNECT jward@hq

CREATE DATABASE LINK sales.division3.example.com 
  CONNECT TO tsmith IDENTIFIED BY radio; 

UPDATE tsmith.emp@sales.division3.example.com 
  SET deptno = 40 
  WHERE deptno = 10; 

The database processes the final statement as follows:

1. The database determines that a complete global object name is referenced in jward's UPDATE statement. Therefore, the system begins searching in the local database for a database link with a matching name.

2. The database finds a matching private database link in the schema jward. Nevertheless, the private database link jward.sales.division3.example.com does not indicate a complete path to the remote sales database, only a remote account. Therefore, the database now searches for a matching public database link.

3. The database finds the public database link in scott's schema. From this public database link, the database takes the service name dbstring.

4. Combined with the remote account taken from the matching private fixed user database link, the database determines a complete path and proceeds to establish a connection to the remote sales database as user tsmith/radio.

5. The remote database can now resolve the object reference to the emp table. The database searches in the tsmith schema and finds the referenced emp table.

6. The remote database completes the execution of the statement and returns the results to the local database.

An example illustrates how the database resolves a partial global object name and determines the appropriate path to the remote database using both a private and public database link.

For this example, assume that:

• The remote database is named sales.division3.example.com.

• The local database is named hq.division3.example.com.

• A directory server (and therefore, global database links) is not available.

• A table emp on the remote database sales is contained in the schema tsmith, but not in schema scott.

• A public synonym named emp resides at remote database sales and points to tsmith.emp in the remote database sales.

• The public database link in "Example of Global Object Name Resolution: Complete Object Name " is already created on local database hq:

  CREATE PUBLIC DATABASE LINK sales.division3.example.com 
    CONNECT TO guest IDENTIFIED BY network 
    USING 'dbstring'; 
  

Consider the following statements issued at local database hq:

CONNECT scott@hq

CREATE DATABASE LINK sales.division3.example.com;

DELETE FROM emp@sales 
  WHERE empno = 4299; 

The database processes the final DELETE statement as follows:

1. The database notices that a partial global object name is referenced in scott's DELETE statement. It expands it to a complete global object name using the domain of the local database as follows:

   DELETE FROM emp@sales.division3.example.com 
     WHERE empno = 4299; 
   

2. The database searches the local database for a database link with a matching name.

3. The database finds a matching private connected user link in the schema scott, but the private database link indicates no path at all. The database uses the connected username/password as the remote account portion of the path and then searches for and finds a matching public database link:

   CREATE PUBLIC DATABASE LINK sales.division3.example.com 
     CONNECT TO guest IDENTIFIED BY network 
     USING 'dbstring'; 
   

4. The database takes the database net service name dbstring from the public database link. At this point, the database has determined a complete path.

5. The database connects to the remote database as scott/password and searches for and does not find an object named emp in the schema scott.

6. The remote database searches for a public synonym named emp and finds it.

7. The remote database executes the statement and returns the results to the local database.

A view, synonym, or PL/SQL program unit (for example, a procedure, function, or trigger) can reference a remote schema object by its global object name.

If the global object name is complete, then the database stores the definition of the object without expanding the global object name. If the name is partial, however, then the database expands the name using the domain of the local database name.

The following table explains when the database completes the expansion of a partial global object name for views, synonyms, and program units:

Global name changes can affect views, synonyms, and procedures that reference remote data using partial global object names.

If the global name of the referenced database changes, views and procedures may try to reference a nonexistent or incorrect database. However, synonyms do not expand database link names at run time, so they do not change.

Scenarios illustrate global name changes.

For example, consider two databases named sales.uk.example.com and hq.uk.example.com. Also, assume that the sales database contains the following view and synonym:

CREATE VIEW employee_names AS 
        SELECT ename FROM scott.emp@hr; 

CREATE SYNONYM employee FOR scott.emp@hr; 

The database expands the employee synonym definition and stores it as:

scott.emp@hr.uk.example.com

• Scenario 1: Both Databases Change Names
  A scenario illustrates a situation in which both global database names change.
• Scenario 2: One Database Changes Names
  A scenario illustrates a situation in which one global database name changes.