1

I have a Java code that connects to Database A. Database A has DB link to Database B. Both database are oracle.

I make a JPA connection to Database A Then I run a query from joining a table from DAtabase A to table in Database B Then Close connection to Database A Simple code.

I am still seeing the DB link connections on Database B is open. It is not closing. Any idea why? I am assuming that it should be internally handled right?

Thoughts?

Marco
  • 83
  • 1
  • 10

1 Answers1

0

If you close a connection to the database all database link connections are automatically closed.

So if you observes open link sessions, they stem from other not yet closed connections.

The database link connection is from performance reasons not closed immediately after each distributed query, but is intentionally left open to be reused in the potential next statement.

Anyway you can excplicitely close the link connection using a call of either

 alter session close database link  link_name

or

DBMS_SESSION.CLOSE_DATABASE_LINK('link_name')

es illustrated here. This is mainly done in case you hit the limit of maximal open links

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • 1
    Thanks @Marmite Bomber 1. Yeah, I am closing connection but the DB links are staying open. My job runs every hour and the multiple links remain open. 2. If DB link connections remain open for performance reasons- is there a way to reuse them, if not, my number of connections are growing and will run out. 3) Should I run another JPA query to - alter session close database link link_name right after my processing? – Marco Apr 20 '20 at 21:39
  • 1) please describe what you observe and how do you reason that the sessions in DB B are related to *closed* session in DB A. (This would be a case for Oracle Support). 2) They are automatically reused in next distributed statement, but only from the session from that they were opened. 3) This could be meaningfull if you know that you use only a single distributed query *followed by a long running local session*. – Marmite Bomber Apr 20 '20 at 21:59
  • 1) I made DBA close all sessions on database. My job runs on the hour- 11:00AM, 12:00PM, 1:00PM. At the exact time - new connection was opened but it never gets closed. I can track them by the time it was opened. After 3 hours three connections were open with an opening timestamp of 11:00AM, 12:00PM, 1:00PM. 2)Everytime a new connection is created - that means new session. The connection is closed after that transaction is complete(as described above)) – Marco Apr 20 '20 at 22:22