Dblink Connection

[Former Member]

We have two oracle 10g servers.

Server1 Configuration (SAP)

Oracle : Oracle 10g

OS : Windows

Server2 Configuration

Oracle : Oracle 10g

OS : Linux

We have created one DBlink from Server1(Windows) to Server2(Linux) for fetching data read only purpose.

The problem find is that after fetching data from server2 the session remain open. It increases sessions on server2(Linux).

How to close session on server2.

What to do?

[stefan_koehler]

Hi Chirag,

>> How to close session on server2.

Not quite sure how you have implemented the DBLink (e.g. via DBCON or manual via native SQL and @<DB_LINK>, etc.), but you have to close the database link, if you want to eliminate that remote session.

Oracle documentation: Developing Applications for a Distributed Database System

The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application or user explicitly terminates them. Note that when you issue a SELECT statement across a database link, a transaction lock is placed on the undo segments. To rerelease the segment, you must issue a COMMIT or ROLLBACK statement.


Terminating remote connections established using database links is useful for disconnecting high cost connections that are no longer required by the application. You can terminate a remote connection and session using the ALTER SESSION statement with the CLOSE DATABASE LINK clause. For example, assume you issue the following transactions:

SAP documentation: http://help.sap.com/abapdocu_70/en/ABAPEXEC_CONNECTION.htm

Regards

Stefan

[Former Member]

We have kernal vesion 720.

Above both options are giving error. These options can be useful in SAP kernal version 4.6D and below.

I had created data link manual via native SQL.

Any other suggestion.

[ACE-SAP]

Hi

Have you tried dropping the link from source system ?

drop database link  <link name>;

You can kill disconnect session on target, or even kill it

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE

Regards