Distributed Database Management

Database Links

Note: If your network uses Oracle Names, a global database link is created on every database on the network to every other database. Therefore, you do not need to create additional database links, as described in this section. See "Database Links wth Oracle Names" later in this chapter.

The generic syntax for creating a database link in SQL is:

CREATE [PUBLIC] DATABASE LINK linkname 
[CONNECT TO username IDENTIFIED BY password] 
USING 'service_name' 

In this syntax:

[PUBLIC]	Specifies a database link available to all users with the CREATE SESSION privilege. If the PUBLIC option is omitted, a private link available only to the creator is created. Note that creating a public database link requires CREATE PUBLIC DATABASE LINK privilege.
linkname	Specifies the name of the database link. If the remote server is in the local server's domain, the link name does not need to include the domain name. However, if the server is in another domain, the link name must include the domain. (The domain is determined by DB_DOMAIN in the initialization parameter file).
CONNECT TO	Optionally specifies a single username and password for all users of the database link to share. If the clause is omitted, the Oracle username and password of the user account using the database link will be used to connect to the remote database server.
username	Specifies a valid Oracle username on the remote database server.
password	Specifies the corresponding password of the username on the remote database server.
service_name	Specifies the service name defined in the TNSNAMES.ORA file or stored in Oracle Names associated with the connect descriptor for the desired database. If the remote server is in the local server's default domain, the service name does not need to include the domain name. However, if the server is in another domain, the service name must include the domain. (The default domain is determined by a parameter in the server's SQLNET.ORA file. See the section on the SQLNET.ORA file in Appendix A of this manual.)

Prior to Oracle7, a database administrator could specify any linkname for a database link. However, with Oracle7 and later, a database link must have the same name as the global database name of the database. Remember that the service name is also the same as the global database name; therefore, the linkname and service name are now the same.

For example, the command for creating a public database link to a database which has the global database name ORCHID.HQ.ACME is as follows:

CREATE PUBLIC DATABASE LINK ORCHID.HQ.ACME
CONNECT TO scott IDENTIFIED BY tiger
USING 'ORCHID.HQ.ACME'

Note: The CONNECT TO username IDENTIFIED BY password clause and the USING 'global_database_name' clause are both optional.

Public Database Links with a Default Connection

CREATE PUBLIC DATABASE LINK NY_FIN.HQ.ACME
USING 'NY_FIN.HQ.ACME'

Figure 5 - 2. Public Database Link with Default Connection

Users connected to OHIO.SALES.ACME can use the NY_FIN.HQ.ACME database link to connect to NY_FIN.HQ.ACME with the same username and password they have on OHIO.SALES.ACME. To access the table on NY_FIN.HQ.ACME called EMP, any user could issue the SQL query:

SQL> SELECT * FROM EMP@NY_FIN.HQ.ACME; 

Note: If the target database were in the default domain of the source database, the user would not need to include the domain in the link name or service name, or in the SELECT command.

This query would initiate a connection from OHIO to NY_FIN using the current username and password to log onto NY_FIN. The query would then be processed on NY_FIN, and the data available to the current user from the table EMP would be returned to OHIO. Each user creates a separate connection to the server. Subsequent queries to that database link by that user would not require an additional logon.

Public Database Links with a Specific Connection

CREATE PUBLIC DATABASE LINK NY_FIN
CONNECT TO FINPUBLIC IDENTIFIED BY NOPASS
USING 'NY_FIN'

Note: The CONNECT TO username IDENTIFIED BY password clause and the USING 'global_database_name' clause are both optional.

Figure 5 - 3. Public Database Link with Specific Connection

Any user connected to OHIO can use the NY_FIN database link to connect to NY_FIN with the common username/password of FINPUBLIC/NOPASS. To access the table in the FINPUBLIC account of NY_FIN called ALL_SALES, any user could issue the SQL query:

SQL> SELECT * FROM ALL_SALES@NY_FIN; 

This query would initiate a connection from OHIO to NY_FIN to the common account FINPUBLIC. The query would be processed on NY_FIN and data from the table ALL_SALES would be returned to OHIO.

Each user creates a separate connection to the common account on the server. Subsequent queries to that database link by that user would not require an additional logon.

Connection Qualifiers

A connection qualifier contains a qualifier name and, optionally, a username and password. To create a connection qualifier, use a statement similar to the following:

CREATE PUBLIC DATABASE LINK NY_FIN@PROFITS
CONNECT TO ACCOUNTS IDENTIFIED BY TAXES
USING 'NY_FIN'

To use the connection qualifier, you append the qualifier name to the service name of the database you want to access.

For example, the following SQL queries use three separate database links to the same database, using different connection qualifiers:

SELECT * FROM EMP@NY_FIN;

SELECT * FROM SCHEDULE@NY_FIN@PROFITS;

SELECT * FROM EMPSALARIES@NY_FIN@FIN;

In this example @PROFITS and @FIN are connection qualifiers.

Dropping a Database Link

DROP DATABASE LINK linkname; 

For example, to drop the database link NY_FIN, the command would be:

DROP DATABASE LINK NY_FIN; 

Finding Available Database Links

Database Links with Oracle Names

SQL> SELECT * FROM EMP@OHIO, DEPT@NY_FIN; 

Explicitly Defined Database Links

Connection Qualifiers

For a more detailed discussion of database links, see Oracle7 Server Distributed Systems, Volume I, and the Oracle Names Administrator's Guide.

Synonyms

CREATE [PUBLIC] SYNONYM_name 
FOR [schema.]object_name[@database_link_name] 

In this syntax:

[PUBLIC]	Specifies that this synonym is available to all users. Omitting this parameter makes a synonym private, and usable only by the creator. Public synonyms can be created only by a user with CREATE PUBLIC SYNONYM system privilege.
synonym_name	Specifies the alternate object name to be referenced by users and applications.
schema	Specifies the schema of the object specified in object_name. Omitting this parameter uses the creator's schema as the schema of the object.
object_name	Specifies either a table, view, sequence, or other name as appropriate.
database_link_name	Specifies the database link which identifies the remote username in which the object specified in object_name is located.

A synonym must be a uniquely named object for its schema. If a schema contains a database object and a public synonym exists with the same name, Oracle always finds the database object when the user that owns the schema references that name.

Because a synonym is merely a reference to the actual object, the security domain of the object is used when the synonym is accessed. For example, a user that has access to a synonym for a specific table must also have privileges on that table to access the data in it. If the user attempts to access the synonym, but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.

Figure 5 - 4 shows two servers, OHIO and NY_FIN, in which a database link from OHIO to NY_FIN and the synonym FOR_SALE provide an alternate object name for use in OHIO to reference the OPEN table in NY_FIN. The database link and the synonym are created as follows:

CREATE PUBLIC DATABASE LINK NY_FIN
CONNECT TO REAL_ESTATE IDENTIFIED BY NOPASS;
USING 'NY_FIN'
CREATE PUBLIC SYNONYM FOR_SALE
FOR OPEN@NY_FIN;

Figure 5 - 4. Using Synonyms for Alternate Object Names

The table OPEN on NY_FIN could be accessed from OHIO using the SQL statement:

SELECT * FROM FOR_SALE; 

Using this database link, the user is logging on to NY_FIN as user REAL_ESTATE. Notice that this public synonym was created by the DBA on behalf of the REAL_ESTATE username. If the table OPEN were owned by another user, such as SALES_MGR, the CREATE SYNONYM statement would have referred to the object as SALES_MGR.OPEN@NY_FIN. Without such a prefix, a table that does not exist in the database link user's schema would return an error, since it would be looking for the OPEN table owned by the REAL_ESTATE user.

Maintaining Location Transparency

This ability to isolate applications from the location of data in a distributed transaction ensures maximum flexibility for future enhancements or changes to the application. For example, if the OPEN table were to move from one database server to another, only the synonym or the database link would need to be changed to identify the new location. The applications would continue to reference the same object name, although they would be connecting to a new location to access the data in that table. Figure 5 - 5 shows the most common method of redefining the location of a table to retain location transparency.

Figure 5 - 5. Redefining Table Location to Retain Location Transparency

CREATE PUBLIC DATABASE LINK NY_TAX
CONNECT TO REALTOR IDENTIFIED BY NOPASS
USING 'NY_TAX'
DROP SYNONYM FOR_SALE;

CREATE PUBLIC SYNONYM FOR_SALE
FOR OPEN@NY_TAX;

To relocate the table, a second database link was created called NY_TAX that connected to a new database with the service name NY_TAX, and the synonym was recreated to reference the NY_TAX database link instead of the NY_FIN database link. Any other tables that were accessed through the NY_FIN database link to NY_FIN would continue to function properly.

Alternatively, if the only table being accessed on NY_FIN were the OPEN table, the synonym could have remained unchanged and the database link redefined to use the service name NY_TAX instead of NY_FIN. Either option is equally effective.

Snapshots

Maintaining snapshots of a master table among the nodes of a distributed database is often a useful feature for the following reasons:

• Queries can be issued against a local snapshot. Therefore, associated query performance is fast because the requested data does not have to be shipped over a network.

• If the database that contains the master table or a database that contains a snapshot experiences a failure or is cut off from the distributed database by a network failure, the other (read-only) copies remain accessible to other users of the system.

Figure 5 - 6. Table Replication Using Snapshots

A snapshot is a full copy or a subset of a table that reflects a recent state of the master table. A snapshot is defined by a distributed query that references one or more master tables, view, or other snapshots. A database that contains a master table is referred to as the master database.

Simple vs. Complex Snapshots Each row in a simple snapshot is based on a single row in a single remote table. Therefore, a simple snapshot's defining query has no GROUP BY or CONNECT BY clauses, or subqueries, joins, or set operations. If a snapshot's defining query contains any of these clauses or operations, it is referred to as a complex snapshot.

Creating Snapshots

CREATE SNAPSHOT emp_sf
   PCTFREE 5 PCTUSED 60
   TABLESPACE users
   STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 50)
   REFRESH FAST
           START WITH sysdate
           NEXT sysdate + 7
   AS SELECT * FROM scott.emp@ny;

Whenever a snapshot is created, it is immediately populated with the rows returned by the query that defines the snapshot. Thereafter, the snapshot is refreshed as specified by the REFRESH clause; see Oracle7 Server Distributed Systems, Volume 2: Replicated Data for more information about refreshing snapshots.

When a snapshot is created, Oracle creates several internal objects in the schema of the snapshot. These objects should not be altered. At the snapshot node, Oracle creates a base table to store the rows retrieved by the snapshot's defining query. Oracle then creates a read-only view of this table that is used whenever queries are issued against the snapshot.

Specifying the Defining Query of a Snapshot The defining query of a snapshot can be any valid query of tables, views, or other snapshots that are not owned by user SYS. The query cannot contain either an ORDER BY or FOR UPDATE clause. Furthermore, simple snapshots are defined using a query that does not contain GROUP BY or CONNECT BY clauses, or join, subquery, or set operations.

The query that defines a snapshot can define a snapshot with a different structure from that of the master table. For example, the following CREATE SNAPSHOT statement creates a local snapshot named EMP_DALLAS, with only the EMPNO, ENAME, and MGR columns of the master table (in New York), and only the rows of the employees in department 10:

CREATE SNAPSHOT emp_dallas
   .
   .
   .
   AS SELECT empno, ename, mgr
      FROM scott.emp@ny
      WHERE deptno = 10;

Privileges Required to Create Snapshots To create a snapshot, the following sets of privileges must be granted as follows:

• To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, and CREATE VIEW (only for simple snapshots) system privileges as well as the appropriate SELECT privilege on the master tables.

• To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT, CREATE ANY TABLE, CREATE ANY VIEW, and CREATE ANY INDEX (only for simple snapshots) system privileges as well as the appropriate SELECT privilege on the master table. Additionally, for the snapshot to be of any use, the owner of the snapshot must have the appropriate SELECT privilege on the master tables

The large set of privileges required to create a snapshot is due to the underlying objects that must also be created on behalf of the snapshot.

Refreshes

Snapshot Logs

A complex snapshot or simple snapshot without a snapshot log must be completely regenerated using the master tables every time the snapshot is refreshed (called a complete refresh).

Creating Snapshot Logs

CREATE SNAPSHOT LOG ON emp
   TABLESPACE users
   STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50);

The snapshot log is always created in the same schema that contains the master table. Since you cannot specify a name for the snapshot log (one is implicitly given by Oracle), uniqueness is not a concern.

If you own the master table, you can create an associated snapshot log if you have the CREATE TABLE and CREATE TRIGGER system privileges. If you are creating a snapshot log for a table in another user's schema, you must have the CREATE ANY TABLE and CREATE ANY TRIGGER system privileges. In either case, the owner of the snapshot log must have sufficient quota in the tablespace intended to hold the snapshot log.

The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log.

Using Snapshots

SELECT * FROM emp;

To query a snapshot, you must have the SELECT object privilege for the snapshot, either explicitly or via a role.

In release 7.0 of the Oracle Server, snapshots are read-only. You cannot issue any INSERT, UPDATE, or DELETE statements when using a snapshot; if you do, an error is returned. Although INSERT, UPDATE, and DELETE statements can be issued against the base table for a snapshot, they can corrupt the snapshot. Never manipulate data in the base table of a snapshot. Updates are allowed on the master table only, which must then be refreshed to update the snapshot.

Creating Views and Synonyms Based on Snapshots Views or synonyms can be defined based on snapshots. The following statement creates a view based on the EMP snapshot:

CREATE VIEW sales_dept AS
   SELECT ename, empno
   FROM emp
   WHERE deptno = 10;

Dropping Snapshots

DROP SNAPSHOT emp;

If you drop the only snapshot of a master table, you should also drop the snapshot log of the master table, if appropriate.

Only the owner of a snapshot, or a user with the DROP ANY SNAPSHOT, DROP ANY TABLE, and DROP ANY VIEW system privileges can drop a snapshot.

For more information about table replication, snapshots, and distributed database design, refer to the following manuals: Oracle7 Server Distributed Systems, Volumes I and II, and the Oracle7 Server Administrator's Guide.