Oracle Dblink causes excessive remote database session

On-site newspaper network company database is not connected, first check the next database processes=1500,session=2200. I think it's very big. This database is not used by a few people.

See the session in V$session most of which machine initiated, found that the provincial company's database initiated the session, looking for the development of carding the next business, the provincial company Synchronous Dblink Operation Network Company table, and is through the WebLogic connection pool.

Oh, a little understand, is dblink caused, WebLogic connection pool is always there, so in the network company end session is not released, if the provincial companies to stop the application, that in the web company end of the session will be released. To verify the idea, do an experiment:

Target: database A is built on dblink and modifies the table on database B.

Environment Preparation:

1. Build Dblink on Database A

Create Public database link To_b
Connect to test_db identified by test_db
Using ' (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.15.150) (PORT = 1521))) (Connect_data = (SID = O RCL))) ';

2. Building a table on database B

CREATE TABLE TEST (ID number);

INSERT into test values (1);

To start the test:

1. On database B Select COUNT (1) from v$session;

2. Update on database A [email protected]To_b set id=1;

Commit

3. On database B select count (1) from v$session; Can see the rise of a

Comparison test:

1. On database B Select COUNT (1) from v$session;

2. Update on database A [email protected]To_b set id=1;

Commit

Alter session close database linkto_b;

3. On database B Select COUNT (1) from v$session; can see no change

Summary: This problem occurs, in the final analysis is the operation Dblink not standardized, after use did not close.

The appendix is an official document:

+Managing a distributed databaseclosing Database Links

If You access a database link in a session and then the link remains open until you close the session. A link is open with the sense, a process is active over each of the remote databases accessed through the link. This situation has the following consequences:

• If the users open sessions and access the same public link in a local database, then the database link connections is open.

• If the users open sessions and each user accesses a private link, then the database link connections is open.

• If One user starts a session and accesses different links, then the database link connections is open.

After you close a session, the links that were active in the session is automatically closed. Occasion to close the link manually. For example, close links When:

• The network connection established by a link was used infrequently in an application.

• The user session must be terminated.

To close a link, issue the following statement, where linkname refers to the name of the link:

linkname;

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

Oracle Dblink causes excessive remote database session