|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
If you access a database link in a session, then 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 databases accessed through the link. This situation has the following consequences:
If 20 users open sessions and access the same public link in a local database, then 20 database link connections are open.
If 20 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 manually. For example, close links when:
The network connection established by a link is used infrequently in an 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 DATABASE LINK linkname;
Note that this statement only closes the links that are active in your current session.
You can drop a database link just as you can drop a table or view. If the link is private, then it must be in your schema. If the link is public, then you must have the
DROP PUBLIC DATABASE LINK system privilege.
The statement syntax is as follows, where dblink is the name of the link:
DROP [PUBLIC] DATABASE LINK dblink;
Connect to the local database using SQL*Plus. For example, enter:
USER_DB_LINKS to view the links that you own. For example, enter:
SELECT DB_LINK FROM USER_DB_LINKS; DB_LINK ----------------------------------- SALES.US.EXAMPLE.COM MKTG.US.EXAMPLE.COM 2 rows selected.
Drop the desired link using the
DROP DATABASE LINK statement. For example, enter:
DROP DATABASE LINK sales.us.example.com;
Connect to the local database as a user with the
DROP PUBLIC DATABASE LINK privilege. For example, enter:
CONNECT SYSTEM@local_db AS SYSDBA
DBA_DB_LINKS to view the public links. For example, enter:
SELECT DB_LINK FROM USER_DB_LINKS WHERE OWNER = 'PUBLIC'; DB_LINK ----------------------------------- DBL1.US.EXAMPLE.COM SALES.US.EXAMPLE.COM INST2.US.EXAMPLE.COM RMAN2.US.EXAMPLE.COM 4 rows selected.
Drop the desired link using the
DROP PUBLIC DATABASE LINK statement. For example, enter:
DROP PUBLIC DATABASE LINK sales.us.example.com;
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 value 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 access 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