Managing Database Links

This section contains the following topics:

Closing Database Links
Dropping Database Links
Limiting the Number of Active Database Link Connections

Closing Database Links

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.

Dropping Database Links

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;

Procedure for Dropping a Private Database Link

Connect to the local database using SQL*Plus. For example, enter:
```
CONNECT scott/tiger@local_db
```

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.

Drop the desired link using the DROP DATABASE LINK statement. For example, enter:
```
DROP DATABASE LINK sales.us.oracle.com;
```

Procedure for Dropping a Public Database Link

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
```

Query 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.ORACLE.COM
SALES.US.ORACLE.COM
INST2.US.ORACLE.COM                            
RMAN2.US.ORACLE.COM
4 rows selected.

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 Connections

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 OPEN_LINKS initialization parameter