Skip Headers

Oracle® Database Administrator's Guide
10g Release 1 (10 .1)
Part Number B10739-01
Go to Documentation Home
Home		 Go to Book List
Book List 		Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
M aster Index		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page< br /> Next
 V iew PDF

30 Managing a Distributed Database

This chapter describes how to manage and maintain a distributed database system and co ntains the following topics:

Managing Global Names in a Distri buted System

In a distributed databa se system, each database should have a unique global database name. Global database names uniquely identify a databa se in the system. A primary administration task in a distributed system is managing the creation and alteration of global database na mes.

This section contains the following topics:

Understanding How Global Database Names Are Formed

A global database name is formed from two components: a database name and a domain. The database name and the domain name are determined by the following initialization parameters at database creation:< /p>

Component Parameter Requirements Example
Database name DB_NAME Must be eight characters or less. sales
Domain containing the database DB_DOMAIN Must follow standard Internet conventions. Levels in domain names must be separated by dots and the order of domain names is from leaf to root, left to right. us.acme.com

These are examples of valid global database names:

DB_NAME DB_DOMAIN Global Database Name
sales au.oracle.com sales.au.oracle.com
sales us.oracle.com s ales.us.oracle.com
mktg< /td> us.oracle.com mktg .us.oracle.com
payroll nonprofit.org payro ll.nonprofit.org

The DB_DOMAIN initi alization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database global name. At this point, the database global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initializat ion parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup.

Determining Whether Global Nami ng Is Enforced

The name that you giv e to a link on the local database depends on whether the remote database that you want to access enforces global naming. If the remot e database enforces global naming, then you must use the remote database global database name as the name of the link. For example, i f you are connected to the local hq server and want to create a link to the remote mfg database, and mfg enforces global naming, then you must use the mfg global database name as the link name.

You can also use service names as part of the database link name. For example, if you use the service names sn1 and sn2 to connect to database hq.acme.com, and hq enforces global naming, then you can create the following link n ames to hq:

To determine whether global na ming on a database is enforced on a database, either examine the database initialization parameter file or query the V$PARAMETE R view. For example, to see whether global naming is enforced on mfg, you could start a session on mfg and then create and execute the following globalnames.sql script (sample output included):

COL NAME FORMAT A12
COL VALUE FORMAT A6
SELECT NAME, VALUE FROM V$PARAMETER
   WHERE NAME = 'global_names'
/

SQL> @globalname
s

NAME         VALUE
------------ ------
global_names FALSE

Viewi ng a Global Database Name

Use the da ta dictionary view GLOBAL_NAME to view the database global name. For example, issue the following:

SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
-------------------------------------------------------------------------------
SALE
S.AU.ORACLE.COM

Changing the Domain in a Global Database Name

Use the ALTER DATABASE statement to change the domain in a database global name. Note that after the database is created, changing the initialization parameter DB_DOMAIN has no effect on t he global database name or on the resolution of database link names.

The following example shows the syntax for the renaming s tatement, where database is a database name and domain is the network domain:

ALTER D
ATABASE RENAME GLOBAL_NAME TO database.domain;

Use the following procedure to change the domain in a global data base name:

  1. Determine the current global database name. For example, issue:

    SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
----------------------------------------------------------------------------
SALES.AU
.ORACLE.COM

  2. Rename the global database name using an ALTER DATABASE statement. For example, enter: 

    ALTER DATABASE RENAME GLOBAL_NAME TO sales.us.oracle.com;

  3. Query the GLOBAL _NAME table to check the new name. For example, enter:

    SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
-
---------------------------------------------------------------------------
SALES.US.ORACLE.COM

Changing a Global Database Name: Scenario

In this scenario, you change the dom ain part of the global database name of the local database. You also create database links using partially specified global names to test how Oracle Database resolves the names. You discover that the database resolves the partial names using the domain part of the c urrent global database name of the local database, not the value for the initialization parameter DB_DOMAIN.

  1. You connect to SALES.US.ACME.COM and query the GLOBAL_NAME data dictionary view to determine the current database global name:

    CONNECT SYSTEM/password@sales.us.acme.com
SELECT
* FROM GLOBAL_NAME;

GLOBAL_NAME
---------------------------------------------------------------------------- 
SALES.US.ACME.COM

  2. You query the V$PARAMETER view to determine the current setting for the DB_DOMAIN initial ization parameter:

    SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain'; 

NAME       VALUE
---
------  -----------
db_domain  US.ACME.COM

  3. You then create a database link to a database called hq, using only a partially-specified global name:

    CREATE DATABASE LINK hq USING 'sales';

    Th e database expands the global database name for this link by appending the domain part of the global database name of the local database to the name of the database specified in the link.

  4. You query USER_DB_LINKS to determine wh ich domain name the database uses to resolve the partially specified global database name:

    SELECT DB_LI
NK FROM USER_DB_LINKS; 

DB_LINK
------------------
HQ.US.ACME.COM

    This result indicates that the domain part of the glob al database name of the local database is us.acme.com. The database uses this domain in resolving partial database link names when the database link is created.

  5. Because you have received word that the sales database will mo ve to Japan, you rename the sales database to sales.jp.acme.com:

    ALTER DATABA
SE RENAME GLOBAL_NAME TO sales.jp.acme.com;
SELECT * FROM GLOBAL_NAME; 

GLOBAL_NAME
------------------------------------------------
---------------------------- 
SALES.JP.ACME.COM

  6. You query V$PARAMETER again and discover that the value of DB_DOMAIN is not changed, although you renamed the domain part of the global database name:

    SELECT NAME, VALUE FROM V$PARAMETER 
  WHERE NAME = 'db_domain'; 

NAME       VALUE
---------  -----------
db_dom
ain  US.ACME.COM

    This result indicates that the value of the DB_DOMAIN initialization parameter is independ ent of the ALTER DATABASE RENAME GLOBAL_NAME statement. The ALTER DATABASE statement determines the domain of the global database name, not the DB_DOMAIN initialization parameter (although it is good practice to alter DB_ DOMAIN to reflect the new domain name).

  7. You create another database link to database supply, and then query USER_DB_LINKS to see how the database resolves the domain part of the global database name of supply:

    CREATE DATABASE LINK supply USING 'supply'; 
SELECT DB_LINK FROM USER_DB_LINKS; 

DB_LINK
------
------------
HQ.US.ACME.COM 
SUPPLY.JP.ACME.COM

    This result indicates that the database resolves the partially specified link name by using the domain jp.acme.com. This domain is used when the link is created because it is the domain part of the global database name of the local database. The database does not use the DB_DOMAIN initialization paramet er setting when resolving the partial link name.

  8. You then receive word that your previous information was faulty: sales will be in the ASIA.JP.ACME.COM domain, not the JP.ACME.COM domain. Consequently, you rena me the global database name as follows:

    ALTER DATABASE RENAME GLOBAL_NAME TO sales.asia.jp.acme.com; 
S
ELECT * FROM GLOBAL_NAME; 

GLOBAL_NAME
---------------------------------------------------------------------------- 
SALES.ASIA.JP.A
CME.COM

  9. You query V$PARAMETER to again check the setting for the parameter DB_DOMAIN: 

    SELECT NAME, VALUE FROM V$PARAMETER 
  WHERE NAME = 'db_domain'; 

NAME        VALUE
----------  -----
------
db_domain   US.ACME.COM

    The result indicates that the domain setting in the parameter file is exactly the same as it was before you issued either of the ALTER DATABASE RENAME statements.

  10. Finally, you create a link to the warehouse database and again query USER_DB_LINKS to determine how the database resolves the p artially-specified global name:

    CREATE DATABASE LINK warehouse USING 'warehouse'; 
SELECT DB_LINK FROM
USER_DB_LINKS; 

DB_LINK
------------------
HQ.US.ACME.COM 
SUPPLY.JP.ACME.COM
WAREHOUSE.ASIA.JP.ACME.COM

    Again, you see that the database uses the domain part of the global database name of the local database to expand the partial link name during link creation.


    Note:

    In order to correct the supply database link, it must be dropped and re-created.


    See Also:

    Oracle Database Reference for more informat ion about specifying the DB_NAME and DB_DOMAIN initialization parameters

Creating Database Links

To support application access to the data and schema objects throughout a distributed database system, you must create all necessary dat abase links. This section contains the following topics:

Obtaining Privileges Necessary for Creating D atabase Links

A database link is a p ointer in the local database that lets you access objects on a remote database. To create a private database link, you must have been granted the proper privileges. The following table illustrates which privileges are required on which database for which type of lin k:

Privilege D atabase Required For
C REATE DATABASE LINK Local Crea tion of a private database link.
CREAT E PUBLIC DATABASE LINK Local C reation of a public database link.
CRE ATE SESSION Remote Creation of any type of database link.

To see which privileges you currently have available, query ROLE_SYS_PRIVS. For example, you could create and execute the following privs.sql< /code> script (sample output included):

SELECT DISTINCT PRIVILEGE AS "Database Link Privileges"
FROM RO
LE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
                     'CREATE PUBLIC DATABASE LINK')
/

SQL
> @privs

Database Link Privileges
----------------------------------------
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREAT
E SESSION

Specifying Link Types

When you create a database link, you must decide who will have access to it. The following sections describe ho w to create the three basic types of links:

Creating Private Database Links

To create a private database link, specify the following (wher e link_name is the global database name or an arbitrary link name):

CREATE DATABASE LINK l
ink_name ...;

Following are examples of private database links:

SQL Statement Result
CREATE DATABASE LINK supply.us.acme.com; A private link using the global database name to the remote supply database.

Th e link uses the userid/password of the connected user. So if scott (identified by tiger) uses the link in a query, the link establishes a connection to the remote database as scott/tiger.

CREATE DATABASE LINK link_2 CONNECT TO jane IDENTIFIED BY doe USING 'us_s upply'; A private fixed user link called link_2 to the database with service n ame us_supply. The link connects to the remote database with the userid/password of jane/doe regardless of the connected user.
CREATE DATABASE LINK link_1 CONNECT TO CURRE NT_USER USING 'us_supply'; A private link called link_1 to the da tabase with service name us_supply. The link uses the userid/password of the current user to log onto the remote databas e.

Note: The current user may not be the same as the connected user, and must be a global user on both databases involved in the link (see "Users of Database Links"). Current user links are part of the Orac le Advanced Security option.



See Also:

Oracle Database SQL Reference for CREATE DATABASE LINK syntax

Creating Public Database Links

To create a public database link, use the keyword PUBLIC (where link_name is the global database name or an arbitrary link name):

CREATE PUBLIC DATABASE LINK link_name ...;

Following are examples of public database links:

< div class="inftblruleinformal">
SQL Stateme nt Result
CREATE PU BLIC DATABASE LINK supply.us.acme.com; A public link to the remote sup ply database. The link uses the userid/password of the connected user. So if scott (identified by tiger) uses the link in a query, the link establishes a connection to the remote database as scott/tiger.
CREATE PUBLIC DATABASE LINK pu_link CONNECT TO CURRE NT_USER USING 'supply'; A public link called pu_link to the dat abase with service name supply. The link uses the userid/password of the current user to log onto the remote database.

Note: The current user may not be the same as the connected user, and must be a global user on both databases inv olved in the link (see "Users of Database Links").

CREATE PUBLIC DATABASE LINK sales.us.acme.com CONNECT TO jane IDENTIFIED BY doe; A public fixed user link to the remote sales database. Th e link connects to the remote database with the userid/password of jane/doe.


See Also:

Oracle Database SQ L Reference for CREATE PUBLIC DATABASE LINK syntax

Creating Global Database Links

In earlier releases, you defined global database links in the Oracle Names server. The Oracle Names server has been deprecated. Now, you can use a directory server in which databases are identified by net service names. In this document these are what are referred to as global database links.

See the Oracle Net Services Administrator's Guide to learn how to create directory entries that act as global database links.

< /a>

Spec ifying Link Users

A database link defines a communication path from one datab ase to another. When an application uses a database link to access a remote database, Oracle Database establishes a database session in the remote database on behalf of the local application request.

When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connec ted user database links.

Creating Fixed User Database Links

To create a fixed user database link, you embed the credentials (in this case, a username and pas sword) required to access the remote database in the definition of the link:

CREATE DATABASE LINK ... C
ONNECT TO username IDENTIFIED BY password ...;

Following are examples of fixed user database links:

SQL S tatement Result
CRE ATE PUBLIC DATABASE LINK supply.us.acme.com CONNECT TO scott AS tiger; A pub lic link using the global database name to the remote supply database. The link connects to the remote database with the userid/password scott/tiger.
CREATE DATABASE LINK foo CONNECT TO jane IDENTIFIED BY doe USING 'finance'; A private fixed user link called foo to the database with service name finance. The link connects to the remote database with the userid/password jane/doe.

When an application uses a fixed user database link, the local server always establishes a connection to a fixed remote schema in the remote database. The local server also sends the fixed user's credentials across the network when an application uses t he link to access the remote database.

Creating Connected User and Current User Database Links

Connected user and current user database links do not include credentials in the definition of the link. The credentials used to connect to th e remote database can change depending on the user that references the database link and the operation performed by the application.< /p>


Note:

For many distribute d applications, you do not want a user to have privileges in a remote database. One simple way to achieve this result is to create a procedure that contains a fixed user or current user database link within it. In this way, the user accessing the procedure temporari ly assumes someone else's privileges.

For an extended con ceptual discussion of the distinction between connected users and current users, see "Users of Dat abase Links".

Creating a Connected User Database Link

To create a connected us er database link, omit the CONNECT TO clause. The following syntax creates a connected user database link, where dbl ink is the name of the link and net_service_name is an optional connect string:

CREATE [S
HARED] [PUBLIC] DATABASE LINK dblink ... [USING 'net_service_name'];

For example, to create a connected user database link, use the following syntax:

CREATE DATABASE LINK sales.division3.acme.com USING 'sal
es';
Creating a Current User Database Link

To cre ate a current user database link, use the CONNECT TO CURRENT_USER clause in the link creation statement. Current user li nks are only available through the Oracle Advanced Security option.

The following syntax creates a current user database link, where dblink is the name of the link and net_service_name is an optional connect string:

CREATE [SHARED] [PUBLIC] DATABASE LINK dblink CONNECT TO CURRENT_USER 
[USING 'net_service_name'];

For example, to create a connected user database link to the sales database, you might use the following syntax:

CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales';


< font face="arial, helvetica, sans-serif">Note:

To use a current user database link, the current user must be a global user on both databases involved in the link.


See Also:

Oracle Database SQL Reference for more syn tax information about creating database links

Using Connection Qualifiers to Specify Service Names Within Link Names

In some situations, you may want to have several database links of the same type (for example, public) that point to the same remote database, yet establish connections to the remote database using different communication pathways. Some cases in which this strategy is useful are:

  • A remote database is part of an Oracle Real Application Clusters configuration, so you d efine several public database links at your local node so that connections can be established to specific instances of the remote dat abase.

  • Some clients connect to the Oracle Database server using TCP/IP while others use DECNET.

To facilitate such functionality, the database lets you create a database link with an optional service name in the database link name. When creating a database link, a service name is specified as the trailing portion of the database link name, separated b y an @ sign, as in @sales. This string is called a connection qualifier.

For exampl e, assume that remote database hq.acme.com is managed in a Oracle Real Application Clusters environment. The hq database has two instances named hq_1 and hq_2. The local database can contain the following public da tabase links to define pathways to the remote instances of the hq database:

CREATE PUBLIC
DATABASE LINK hq.acme.com@hq_1
  USING 'string_to_hq_1';
CREATE PUBLIC DATABASE LINK hq.acme.com@hq_2
  USING 'string_to_hq_2';
CREAT
E PUBLIC DATABASE LINK hq.acme.com
  USING 'string_to_hq';

Notice in the first two examples that a service name is simply a part of the database link name. The text of the service name does not necessarily indicate how a connection is to be established; this information is specified in the service name of the USING clause. Also notice that in the third example, a service name is not specified as part of the link name. In this case, just as when a service name is specified as part of the link name, the instance is determined by the USING string.

To use a service name to specify a particular instance, include the s ervice name at the end of the global object name:

SELECT * FROM scott.emp@hq.acme.com@hq_1

Note that in this example, there are two @ symbols.

Using Shared Database Links

Every application that references a r emote server using a standard database link establishes a connection between the local database and the remote database. Many users r unning applications simultaneously can cause a high number of connections between the local and remote databases.

Shared datab ase links enable you to limit the number of network connections required between the local server and the remote server.

This section contains the following topics:

  • Determining Whether to Use Shared Database Links

  • Creating Shared Database Links

  • Configuring Shared Database Links

    < div align="center">


    Determining Whether to Use Shared Datab ase Links

    Look carefully at your app lication and shared server configuration to determine whether to use shared links. A simple guideline is to use shared database links when the number of users accessing a database link is expected to be much larger than the number of server processes in the local da tabase.

    The following table illustrates three possible configurations involving database links:

    See Also:

    "What Are Shared Database Links?" for a conceptual overview of shared database links
    Link Type Server Mode Consequences
    Nonshared Dedicated/shared server If your application uses a standard public da tabase link, and 100 users simultaneously require a connection, then 100 direct network connections to the remote database are requir ed.
    Shared Shared server If 10 shared server processes exist in the local s hared server mode database, then 100 users that use the same database link require 10 or fewer network connections to the remote serv er. Each local shared server process may only need one connection to the remote server.
    Shared Dedicated If 10 clients connect to a local dedicated server, and each client has 10 sessions on the same connecti on (thus establishing 100 sessions overall), and each session references the same remote database, then only 10 connections are neede d. With a nonshared database link, 100 connections are needed.

    Shared database links are not useful in all situations. Assume that only one user accesses the remote server. If this user def ines a shared database link and 10 shared server processes exist in the local database, then this user can require up to 10 network c onnections to the remote server. Because the user can use each shared server process, each process can establish a connection to the remote server.

    Clearly, a nonshared database link is preferable in this situation because it requires only one network connect ion. Shared database links lead to more network connections in single-user scenarios, so use shared links only when many users need t o use the same link. Typically, shared links are used for public database links, but can also be used for private database links when many clients access the same local schema (and therefore the same private database link).


    < /table>

    Creating Shared Dat abase Links

    To create a shared datab ase link, use the keyword SHARED in the CREATE DATABASE LINK state ment:

    CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY 
password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED BY password
[USING 'service_na
me'];

    The following example creates a fixed user, shared link to database sales, connecting as sc ott and authenticated as keith:

    CREATE SHARED DATABASE LINK link2sales
CONNECT TO s
cott IDENTIFIED BY tiger
AUTHENTICATED BY keith IDENTIFIED BY richards
USING 'sales';

    Whenever you use the keyword SHARED, the clause AUTHENTICATED BY is required. The schema specified in the AUTHENTICATED BY clause is only used for security reasons and can be considered a dummy schema. It is not affected when using shared database links, nor doe s it affect the users of the shared database link. The AUTHENTICATED BY clause is required to prevent unauthorized clients from masquerading as a database link user and gaining access to privileged information.


    Note:

    In a multitiered environment, there is a restriction that if you use a shared database link to connect to a remote database, then that remote database cannot link to another database with a data base link that cannot be migrated. That link must use a shared server, or it must be another shared database link.

    See Also:

    Oracle Database SQL Reference for inf ormation about the CREATE DATABASE LINK statement

Configuring Shared Database Links

You can configure shared database links in the following ways:

  • Creating Shared Links to Dedicated Servers

  • Creating Shared Links to Shared Servers

    • Creating Shared Links to Dedicated Serv ers

    In the configuration illustrated in Figure 30-1, a shared server process in the local server owns a dedicated remote server process. The adva ntage is that a direct network transport exists between the local shared server and the remote dedicated server. A disadvantage is th at extra back-end server processes are needed.


    Note:

    The remote server can either be a shared server or dedicated server. There is a dedicated connection betw een the local and remote servers. When the remote server is a shared server, you can force a dedicated server connection by using the (SERVER=DEDICATED) clause in the definition of the service name.

    Figure 30-1 A Shared Database Link to Dedicated S erver Processes

    Description of admin039.gif follows
    Description of the illustration admin039.gif

    Creating Shared Links to Shared Servers

    The conf iguration illustrated in Figure 30-2 uses shared server processes on the remote server. This configuration eliminates the need for more dedicated servers, but r equires the connection to go through the dispatcher on the remote server. Note that both the local and the remote server must be conf igured as shared servers.

    Figure 30-2 Shared Database Link to Shared Server

    Description of admin038.gif follows
    Description of the illustration admin038.gif


    See Also:

    Oracle Net Services Administrator's Guide for information about the shared server option

    Managing Database Links

    This section contains the following topics:

    Closing Database Links

    If you access a database link in a session, t hen the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote dat abases accessed through the link. This situation has the following consequences:

    • If 20 users open sessio ns and access the same public link in a local database, then 20 database link connections are open.

    • If 2 0 users open sessions and each user accesses a private link, then 20 database link connections are open.

    • If one user starts a session and accesses 20 different links, then 20 database link connections are open.

    After you close a session, the links that were active in the session are automatically closed. You may have occasion to close the link manuall y. For example, close links when:

    • The network connection established by a link is used infrequently in a n application.

    • The user session must be terminated.

    If you want to close a link, issue the following statement, where linkname refers to the name of the link:

    ALTER SESSION CLOSE DA
TABASE LINK linkname;

    Note that this statement only closes the links that are active in your current session.

    < h3>Dropping Database Links

    You can drop a database link just as you can drop a table or view. If the link is pr ivate, then it must be in your schema. If the link is public, then you must have the DROP PUBLIC DATABASE LINK system pr ivilege.

    The statement syntax is as follows, where dblink is the name of the link: 

    DROP [PUBLIC] DATABASE LINK dblink;

    Procedure for Dropping a Private Database Link

    1. Connect to the local database using SQL*Plus. For ex ample, enter:

      CONNECT scott/tiger@local_db

    2. Query USER_DB_LINKS to view the links that you own. For example, enter:

      SELECT DB_LINK FROM USER_DB_LINKS;

DB_LINK
---
--------------------------------
SALES.US.ORACLE.COM
MKTG.US.ORACLE.COM
2 rows selected.

    3. Drop the desired link using the DROP DATABASE LINK statement. For example, enter:

      DROP DATABASE LINK sales.us.or
acle.com;

    Procedure for Dropping a Public Database Link

    1. Connect to the local database as a user with the DROP PUBLIC DATABASE LINK privilege. For example, enter:

      CONNECT SYSTEM/password@local_db AS SYSDBA

    2. Query DBA_DB_LINKS to view the public links. For example, enter:

      SELECT DB_LINK FROM USER_DB_LINKS
  WHERE OWNER = 'PUB
LIC';

DB_LINK
-----------------------------------
DBL1.US.ORACLE.COM
SALES.US.ORACLE.COM
INST2.US.ORACLE.COM
    
RMAN2.US.ORACLE.COM
4 rows selected.

    3. Drop the desired link using the DROP PUBLIC DATABASE LINK statement. For example, enter:

      DROP PUBLIC DATABASE LINK sales.us.oracle.com;

    Limiting the Number of Active Database Link Conne ctions

    You can limit the number of connections from a user process to remote databases using the static initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.

    Note the following considerations for setting this parameter:

    • The valu e should be greater than or equal to the number of databases referred to in a single SQL statement that references multiple databases .

    • Increase the value if several distributed databases are accessed over time. Thus, if you regularly acc ess three databases, set OPEN_LINKS to 3 or greater.

    • The default value for OPEN_LINKS is 4. If OPEN_LINKS is set to 0, then no distributed transactions are allowed.


      See Also:

      Oracle Database Reference for more information about the OPEN_LIN KS initialization parameter

    Viewing Information About Database Links

    The data dictionary of each database stores the definitions of all the database links in th e database. You can use data dictionary tables and views to gain information about the links. This section contains the following top ics:

    Determining Which Links Are in the Database

    The following views show the database links that have been defined at the local database and stor ed in the data dictionary:

    Vi ew Purpose
    DBA_DB_L INKS Lists all database links in th e database.
    ALL_DB_LINKS Lists all database links accessible to the con nected user.
    USER_DB_LINKS Lists all database links owned by the connec ted user.

    These data dictionary views contain the same b asic information about database links, with some exceptions:

    Column Which Views? Description
    OWNER All except USER_* The user who created the database link. If the link is public, then the user is listed as PUBLIC.
    DB_LINK All T he name of the database link.
    USERNA ME All If the link definit ion includes a fixed user, then this column displays the username of the fixed user. If there is no fixed user, the column is N ULL.
    PASSWORD Only USER_* The password for logging into the remote database.
    H OST All The net service na me used to connect to the remote database.
    CREATED All Creatio n time of the database link.

    Any user can query US ER_DB_LINKS to determine which database links are available to that user. Only those with additional privileges can use the ALL_DB_LINKS or DBA_DB_LINKS view.

    The following script queries the DBA_DB_LINKS view to access link information:

    COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT
A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/

    Here, the script is invoked and the resulting output is shown:

    SQL>@link_script

OWNER      DB_LINK                        USER     SERVICE CREATED
----
------ ------------------------------ -------- ------- ----------
SYS        TARGET.US.ACME.COM             SYS      inst1   23-JUN-9
9
PUBLIC     DBL1.UK.ACME.COM               BLAKE    ora51   23-JUN-99
PUBLIC     RMAN2.US.ACME.COM                       inst2   23-
JUN-99
PUBLIC     DEPT.US.ACME.COM                        inst2   23-JUN-99
JANE       DBL.UK.ACME.COM                BLAKE    ora51
  23-JUN-99
SCOTT      EMP.US.ACME.COM                SCOTT    inst2   23-JUN-99
6 rows selected.

    Authorization for Viewing Password In formation

    Only USER_DB_LINKS contains a column for password information. However, if you are an administrative user (SYS or users who connect < code>AS SYSDBA), then you can view passwords for all links in the database by querying the LINK$ table. If you ar e not an administrative user, you can be authorized to query the LINK$ table by one of the following methods:

      < li type="disc">

      Being granted specific object privilege for the LINK$ table

    • Being grante d the SELECT ANY DICTIONARY system privilege


      See Also:

      Oracle Databas e Security Guide for more information about privileges necessary to view objects in the SYS schema

    Viewing Password Information

    You can create and run the following script in SQL*Plus to obtain password information (sample output included):

    COL USERID FORMAT A10
COL PASSWORD FORMAT A10
SELECT USERID,PASSWORD
  FROM SYS.LINK$
  WHERE PASSWORD IS NOT NULL
/

SQL>@linkpwd

USERID     PASSWORD
---------- ----------
SYS        ORACLE
BLAKE      TYGER
SCOTT      TIGER
3 rows selected.

    Viewing Authentication Passwords

    It is possible to view AUTHENTICATED BY ... IDENTIFIED BY ... usernames and passwords for all links in the database by querying the LINK$ table. You can create and run the following script in SQL*Plus to obtain password information (sample output included):

    COL AUTHUSR F
ORMAT A10
COL AUTHPWD FORMAT A10
SELECT AUTHUSR AS userid, AUTHPWD AS password
  FROM SYS.LINK$
  WHERE PASSWORD IS NOT NULL
/

SQL&g
t; @authpwd

USERID     PASSWORD
---------- ----------
ELLIE      MAY
1 row selected.

    You can also view the link and pass word information together in a join by creating and executing the following script (sample output included):

    COL OWNER FORMAT A8
COL DB_LINK FORMAT A15
COL USERNAME FORMAT A8 HEADING "CON_USER"
COL PASSWORD FORMAT A8 HEADING "CON_PWD"
C
OL AUTHUSR FORMAT A8 HEADING "AUTH_USER"
COL AUTHPWD FORMAT A8 HEADING "AUTH_PWD"
COL HOST FORMAT A7 HEADING "SERVICE"
COL CREATED FO
RMAT A10

SELECT DISTINCT d.OWNER,d.DB_LINK,d.USERNAME,l.PASSWORD,
                l.AUTHUSR,l.AUTHPWD,d.HOST,d.CREATED
FROM DBA_DB_L
INKS d, SYS.LINK$ l
WHERE PASSWORD IS NOT NULL
AND d.USERNAME = l.USERID
/

SQL> @user_and_pwd

OWNER    DB_LINK         CON_USER
CON_PWD  AUTH_USE AUTH_PWD SERVICE CREATED
-------- --------------- -------- -------- -------- -------- ------- ----------
JANE     D
BL.ACME.COM    BLAKE    TYGER    ELLIE    MAY      ora51   23-JUN-99
PUBLIC   DBL1.ACME.COM   SCOTT    TIGER                      ora
51   23-JUN-99
SYS      TARGET.ACME.COM SYS      ORACLE                     inst1   23-JUN-99

    Determining Which Link Connections Are Open

    You may find it useful to determine which database link connections are currently open in your session. Note that if you connect as SYSDBA, you cannot query a view to determine all the links open for all sessions; you can only a ccess the link information in the session within which you are working.

    The following views show the database link connections that are currently open in your current session:

    View Purpose
    V$DBLINK Lists all open database links in your session, that is, all database links with the IN_TRANSACTIO N column set to YES.
    < a id="sthref3762" name="sthref3762">GV$DBLINK Lists all open datab ase links in your session along with their corresponding instances. This view is useful in an Oracle Real Application Clusters config uration.

    These data dictionary views contain the same ba sic information about database links, with one exception:

    < tr align="left" valign="top">
    Column Which Views? Description
    DB_LINK All The name of the database link.
    OWNER_ID All The owner of the database link.
    LOGGED_ON All Whether the database link is currently logged on.
    HETEROGENEOUS All Whether the database link is homogeneous (NO) or heterogeneous (YES).
    PROTOCOL All The communication protocol for the database link.
    OPEN_CURSORS All Whether cursors are open for the database link.
    IN_TRANSACTION All Whether the database link is accessed in a transac tion that has not yet been committed or rolled back.
    UPDATE_SENT All Whether there was an update on the database link.
    COMMIT_POINT_STRENGTH All The commit point strength of the transactions using the database link.
    INST_ID G V$DBLINK only The instance from which the view information was obtained.

    For example, you can create and execute the script below t o determine which links are open (sample output included):

    COL DB_LINK FORMAT A25
COL OWNER_ID FORMAT 9
9999 HEADING "OWNID"
COL LOGGED_ON FORMAT A5 HEADING "LOGON"
COL HETEROGENEOUS FORMAT A5 HEADING "HETER"
COL PROTOCOL FORMAT A8
COL O
PEN_CURSORS FORMAT 999 HEADING "OPN_CUR"
COL IN_TRANSACTION FORMAT A3 HEADING "TXN"
COL UPDATE_SENT FORMAT A6 HEADING "UPDATE"
COL CO
MMIT_POINT_STRENGTH FORMAT 99999 HEADING "C_P_S"

SELECT * FROM V$DBLINK
/

SQL> @dblink

DB_LINK                   OWNID LOGON HE
TER PROTOCOL OPN_CUR TXN UPDATE  C_P_S
------------------------- ------ ----- ----- -------- ------- --- ------ ------
INST2.ACME.COM
                 0 YES   YES    UNKN          0 YES YES       255

    Creating Location Transparency

    After you have c onfigured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. I n other words, users can access remote objects as if they were local objects. The following sections explain how to hide distributed functionality from users:

    Using Views to Create Location Transparency

    Local views can provid e location transparency for local and remote tables in a distributed database system.

    For example, assume that table emp is stored in a local database and table dept is stored in a remote database. To make these tables transparent to users of the system, you can create a view in the local database that joins local and remote data:

    CRE
ATE VIEW company AS
   SELECT a.empno, a.ename, b.dname 
   FROM scott.emp a, jward.dept@hq.acme.com b 
   WHERE a.deptno = b.deptno;

    Figure 30-3 Views and Location Transparency

    Description of admin030.gif follows
    Description of the il lustration admin030.gif

    When users access this view, they do not need to know where the data is physically stored, or if data from more than one table is being accessed. Thus, it is easier for them to get required in formation. For example, the following query provides data from both the local and remote database table:

    SELECT * FROM company;

    The owner of the local view can grant only those object privileges on the local view that have been granted by the remote user. (The remote user is implied by the type of database link). This is similar to privilege management f or views that reference local data.

    Using Synonyms to Create Location Transparency

    Synonyms are useful in both distributed and nondistributed environments because they hide the identity of the underlying object, including its location in a distributed database system. If you must rename or move the underlying object, you only need to redefine the synonym; applications based on the synonym continue to function normally. Synonyms a lso simplify SQL statements for users in a distributed database system.

    < /a>

    Creating Synonyms

    You can create synonyms for the f ollowing:

    • Tables

    • Types

    • Views

    • Materialized views

    • Sequences

    • Procedures

    • Functions

    • Packages

    All synonyms are schema objects that are stored in the data di ctionary of the database in which they are created. To simplify remote table access through database links, a synonym can allow singl e-word access to remote data, hiding the specific object name and the location from users of the synonym.

    The syntax to create a synonym is: 

    CREATE [PUBLIC] synonym_name 
FOR [schema.]object_name[@database_l
ink_name];

    where:

    • PUBLIC is a keyword specifying that this synonym is ava ilable 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 schema o f the creator as the schema of the object.

    • object_name specifies either a table, v iew, sequence, materialized view, type, procedure, function or package as appropriate.

    • databas e_link_name specifies the database link identifying the remote database and schema in which the object specified in < code>object_name is located.

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


    Example: Creating a Public Syno nym

    Assume that in every database in a distributed database system, a public synonym is defined for the scott.emp table stored in the hq database: 

    CREATE PUBLIC SYNONYM emp FOR scott.emp@hq.acme.com;

    You can design an employee management application without regar d to where the application is used because the location of the table scott.emp@hq.acme.com is hidden by the public synon yms. SQL statements in the application access the table by referencing the public synonym emp.

    Furthermore, if yo u move the emp table from the hq database to the hr database, then you only need to change the public synonyms on the nodes of the system. The employee management application continues to function properly on all nodes.

    Managing Privileges and Synonyms

    A synonym is a reference to an actual object. A user who has access to a synonym for a particular schema object must also have privileges on the underlying schema object itself. For example, if the use r attempts to access a synonym but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.

    Assume scott creates local synonym emp as an alias for remote object scott.emp@sales.acme.com. scott cannot grant object privileges on the synonym t o another local user. scott cannot grant local privileges for the synonym because this operation amounts to granting pri vileges for the remote emp table on the sales database, which is not allowed. This behavior is different fr om privilege management for synonyms that are aliases for local tables or views.

    Therefore, you cannot manage local privileges when synonyms are used for location transparency. Security for the base object is controlled entirely at the remote node. For exampl e, user admin cannot grant object privileges for the emp_syn synonym.< /a>

    Unlike a database link referenced in a v iew or procedure definition, a database link referenced in a synonym is resolved by first looking for a private link owned by the sch ema in effect at the time the reference to the synonym is parsed. Therefore, to ensure the desired object resolution, it is especiall y important to specify the schema of the underlying object in the definition of a synonym.

    Using Procedures to Create Location Transparency

    PL/SQL program units called procedures can provide location transparency. You have these opti ons:

    Using Local Procedures to Reference Remote Data

    Procedures or functions (either standalone or in packages) can contain SQL statements that reference remote data . For example, consider the procedure created by the following statement:

    CREATE PROCEDURE fire_emp (en
um NUMBER) AS 
BEGIN 
  DELETE FROM emp@hq.acme.com 
  WHERE empno = enum;
END;

    When a user or application calls the fire_emp procedure, it is not apparent that a remote table is being modified.

    A second layer of location transparenc y is possible when the statements in a procedure indirectly reference remote data using local procedures, views, or synonyms. For exa mple, the following statement defines a local synonym:

    CREATE SYNONYM emp FOR emp@hq.acme.com;

    Given this synonym, you can create the fire_emp procedure using the following statement:

    CREATE PROCEDURE fire_emp (enum NUMBER) AS 
BEGIN 
  DELETE FROM emp WHERE empno = enum; 
END;

    If you rename or move the table emp@hq, then you only need to modify the local synonym that references the table. None of the procedures and applications that call the procedure require modification.

    Using Local Procedures to Call Remote Procedures

    You can use a local procedure to call a remote procedure. The remote procedure c an then execute the required DML. For example, assume that scott connects to local_db and creates the follo wing procedure:

    CONNECT scott/tiger@local_db

CREATE PROCEDURE fire_emp (enum NUMBER) 
AS 
BEGIN 
  EXE
CUTE term_emp@hq.acme.com;
END;

    Now, assume that scott connects to the remote database and creates the remo te procedure:

    CONNECT scott/tiger@hq.acme.com

CREATE PROCEDURE term_emp (enum NUMBER) 
AS 
BEGIN 
  DE
LETE FROM emp WHERE empno = enum;
END;

    When a user or application connected to local_db calls the fire _emp procedure, this procedure in turn calls the remote term_emp procedure on hq.acme.com.

    Using Local Synonyms to Reference Remote Procedures

    For example, scott connects to the local sales.acme.com database and creates the following procedure: 

    CREATE PROCEDURE fire_emp (enum NUMBER) AS 
BEGIN 
DELETE FROM emp@hq.acme.com
 
WHERE empno = enum; 
END;

    User peggy then connects to the supply.acme.com database and creat es the following synonym for the procedure that scott created on the remote sales database:

    SQL> CONNECT peggy/hill@supply
SQL> CREATE PUBLIC SYNONYM emp FOR scott.fire_emp@sales.acme.com;

    A local user on supply can use this synonym to execute the procedure on sales.

    Managing Statement Transparency< /font>

    The database allows the following standard DML statements to reference remote tables:

    • SELECT (queries)

    • INSERT

    • UPDATE

    • DELETE

    • SELECT ... FOR UPDATE (not always supported in Het erogeneous Systems)

    • LOCK TABLE

    Queries including joins, aggregates, subqueries, and SELECT ... FOR UPDATE can reference any number of local and remote tables and v iews. For example, the following query joins information from two remote tables:

    SELECT e.empno, e.enam
e, d.dname 
  FROM scott.emp@sales.division3.acme.com e, jward.dept@hq.acme.com d 
  WHERE e.deptno = d.deptno;

    In a hom ogeneous environment, UPDATE, INSERT, DELETE, and LOCK TABLE statements can refer ence both local and remote tables. No programming is necessary to update remote data. For example, the following statement inserts ne w rows into the remote table emp in the scott.sales schema by selecting rows from the emp tabl e in the jward schema in the local database: 

    INSERT INTO scott.emp@sales.division3.acme.com 
  SELECT * FROM jward.emp;

    Restrictions for Statement Transparency:

    Several restrictions apply to statement transparency.

    • Data manipulation language stat ements that update objects on a remote non-Oracle Database system cannot reference any objects on the local Oracle Database. For exam ple, a statement such as the following will cause an error to be raised:

      INSERT INTO remote_table@link
as SELECT * FROM local_table;

    • Within a single SQL statement, all referenced LONG and LONG RAW columns, sequences, updated ta bles, and locked tables must be located at the same node.

    • The database does not allow remote DDL stateme nts (for example, CREATE, ALTER, and DROP) in homogeneous systems except through remote execut ion of procedures of the DBMS_SQL package, as in this example:

      DBMS_SQL.PARSE@link_name(cr
s, 'drop table emp', v7);

      Note that in Heterogeneous Systems, a pass-through facility lets you execute DDL.

    • The LIST CHAINED ROWS clause of an ANALYZE statement cannot reference remote tables.

    • In a distributed database system, the database always evaluates environmentally-dependent SQL functions such a s SYSDATE, USER, UID, and USERENV with respect to the local server, no matter whe re the statement (or portion of a statement) executes.



    • A number of performance restrictions relate to access o f remote objects:

      • Remote views do not have statistical data.

      • Queries on partitioned tables may not be optimized.

      • No more than 20 indexes are considered for a remote table.

      • No more than 20 columns are used for a composite index.

    • There is a restriction in the Oracle Database implementation of distributed read consistency that can cause one node to be in the past with r espect to another node. In accordance with read consistency, a query may end up retrieving consistent, but out-of-date data. See "Managing Read Consistency" to learn how to manage this problem.


      • Note:

      Oracle Database supports the USERENV function for queries only.

      See Also:

      PL/SQL Packages and Types Reference for more information about the DBMS_SQL package

    Managing a Distributed Database: Examples

    This section presents examples illustrating management of database links:

    Example 1: Creating a Public Fixed User Database Link

    The following statements connec t to the local database as jane and create a public fixed user database link to database sales for sc ott. The database is accessed through its net service name sldb:

    CONNECT jane/doe@local

CREATE PUBLIC DATABASE LINK sales.division3.a
cme.com
  CONNECT TO scott IDENTIFIED BY tiger
  USING 'sldb';

    After executing these statements, any user connected to th e local database can use the sales.division3.acme.com database link to connect to the remote database. Each user connect s to the schema scott in the remote database.

    To access the table emp table in scott's remote schema, a user can issue the following SQL query:

    SELECT * FROM emp@sales.division3.acme.com;

<
/pre>


    Note that each application or user session creates a separate connection to the common account on the server. The connectio
n to the remote database remains open for the duration of the application or user session.

    Example 2: Creating a Public Fixed User Shared Database Link

    The following example connects to the local database as dana and creates a public link to the sales da tabase (using its net service name sldb). The link allows a connection to the remote database as scott and authenticates this user as scott:

    CONNECT dana/sculley@local

CREATE SHARED PUBLIC DATABAS
E LINK sales.division3.acme.com
  CONNECT TO scott IDENTIFIED BY tiger 
  AUTHENTICATED BY scott IDENTIFIED BY tiger
  USING 'sldb';

    Now, any user connected to the local shared server can use this database link to connect to the remote sales database through a shared server process. The user can then query tables in the scott schema.

    In the preceding e xample, each local shared server can establish one connection to the remote server. Whenever a local shared server process needs to a ccess the remote server through the sales.division3.acme.com database link, the local shared server process reuses estab lished network connections.

    Example 3: Creating a Public Connected Use r Database Link

    The following exampl e connects to the local database as larry and creates a public link to the database with the net service name sldb :

    CONNECT larr
y/oracle@local

CREATE PUBLIC DATABASE LINK redwood
  USING 'sldb';

    Any user connected to the local database can use the redwood database link. The connected user in the local database who uses the database link determines the remote schema.

    If scott is the connected user and uses the database link, then the database link connects to the remote schema scott. If fox is the connected user and uses the database link, then the database link connects to remote s chema fox.

    The following statement fails for local user fox in the local database when the remote sc hema fox cannot resolve the emp schema object. That is, if the fox schema in the sales.d ivision3.acme.com does not have emp as a table, view, or (public) synonym, an error will be returned.

    CONNECT fox/mulder@local

SELECT * FROM emp@redwood;

    Example 4: Creating a Public Connected User Shared Database Link

    The following example connects to the local database as neil and creates a shared, pu blic link to the sales database (using its net service name sldb). The user is authenticated by the userid/ password of crazy/horse. The following statement creates a public, connected user, shared database link:

    CONNECT neil/young@local

CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com 
  AUTHENTICATED BY crazy IDENTI
FIED BY horse
  USING 'sldb';

    Each user connected to the local server can use this shared database link to connect to the remote database and query the tables in the corresponding remote schema.

    Each local, shared server process establishes one co nnection to the remote server. Whenever a local server process needs to access the remote server through the sales.division3.ac me.com database link, the local process reuses established network connections, even if the connected user is a different user .

    If this database link is used frequently, eventually every shared server in the local database will have a remote connection . At this point, no more physical connections are needed to the remote server, even if new users use this shared database link.

    Example 5: Creating a Public Current User Database Link

    The following example connects to the local database as the connected user and creates a public link to the sales database (using its net service name sldb). The following statement creates a public current user database link:

    CONNECT bart/simpson@local

CREATE PUB
LIC DATABASE LINK sales.division3.acme.com
  CONNECT TO CURRENT_USER
  USING 'sldb';

    Note:

    To use this link, the current user must be a global user.

    The consequences of t his database link are as follows:

    Assume scott creates local procedure fire_emp that deletes a row f rom the remote emp table, and grants execute privilege on fire_emp to ford.

    CONNECT scott/tiger@local_db

CREATE PROCEDURE fire_emp (enum NUMBER) 
AS
BEGIN
   DELETE FROM emp@sales.division3.acme.c
om
   WHERE empno=enum;
END;

GRANT EXECUTE ON fire_emp TO ford;

    Now, assume that ford connects to the local database and runs scott's procedure:

    CONNECT ford/fairlane@local_db

EXECUTE PROCEDURE sc
ott.fire_emp (enum 10345);

    When ford executes the procedure scott.fire_emp, the procedure runs under scott's privileges. Because a current user database link is used, the connection is established to scott's remote schema, not ford's remote schema. Note that scott must be a global user while ford does not have to be a global user.


    Note:

    If a connected user database link were used instead, the connection would be to ford's remote schema. For more information about invoker rights and privileges, see the PL/SQL User's Guide and Reference.

    You can accomplish the same result by using a fixed user database link to scott's remote schema. With fixed user da tabase links, however, security can be compromised because scott's username and password are available in readable forma t in the database.

    Go to previous page
    Previous    		 Go to next page
    Next
    		 Oracle
    Copyright © 2001, 2003 Oracle Corporation All Rights Reserved.
    Go to Documentation Home
    Home    		 Go to Book List
    Book List     		Go to Table of Contents
    Contents    		 Go to Index
    Index    		 Go to Master Index
    Master Index    		 Go to Feedback page
    Feedback