I'm building a J2EE web apps with Oracle database. The app server uses JDBC connection pooling to Oracle database A (e.g. 20 connections max), and some of the queries performed use tables in remote Oracle database B via database link.
If the App Server creates 20 connections to database A, and all queries are looking up data in database B, will database A create 20 connections to database B? Or all queries are through the same connection tunnel?
There's a one-to-one relationship between the local and remote database sessions. There has to be for the following situations: Session A1 on the local DB runs INSERT INTO table@B VALUES ('A1'); That insert is part of Session A1's transaction.
Session A2 comes along and does a SELECT * FROM table@B. Because Session A1 hasn't committed yet, session A2 shouldn't see the row. Therefore it needs a remote session that is distinct from that belonging to A1.
Equally you could have all 20 sessions on database A running queries concurrently on database B.
There is a DBMS_SESSION.CLOSE_DATABASE_LINK procedure which will disconnect the remote connection. I had some problems with that (9i database) when it refused to close them claiming 'outstanding transactions' even immediately after a commit. It seemed to relate to cached PL/SQL cursors. It may no longer be an issue.