Oracle dblink causes too many remote database sessions

Oracle dblink causes too many remote database sessions

The database of the on-site reporting network company could not be connected. I checked the database processes = 1500 and session = 2200 first. I think it is very big. Few people use this database.

Check the machine where the session in the v $ session is initiated at most, and find the session Initiated by the database of the provincial company. Find the development and sort out the business. The provincial company synchronizes the table of the dblink operation network company, the connection pool of weblogic is used.

Oh, a bit clear, it is caused by dblink. the weblogic connection pool always exists, so the session on the network company side will not be released. If the provincial company stops all applications, the sessions at the network company end will be released. To verify the idea, do a test:

Objective: To create A dblink on Database A and modify the table on Database B.

Environment preparation:

1. Create dblink on database

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 = orcl )))';

2. Create a table on Database B

Create table TEST (id number );

Insert into test values (1 );

Start test:

1. select count (1) from v $ session on database B;

2. update test @ TO_ B set id = 1 on database;

Commit;

3. select count (1) from v $ session on database B; you can see that

Comparison test:

1. select count (1) from v $ session on database B;

2. update test @ TO_ B set id = 1 on database;

Commit;

Alter session close database link TO_ B;

3. select count (1) from v $ session on database B; you can see that no change has been made.

Conclusion: in the final analysis, this problem occurs because the dblink operation is not standardized and is not disabled after it is used.

The appendix is the official document:

32 Managing a Distributed DatabaseClosing 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.

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.