How to close db links opened by application threads
Here is the problem:
I have Remedy application and its database which uses db link to apps2 database to pool some data from it.
The way Remedy operates is to pick existing Oracle session from the pool and use it for one off query. This session opens db link to apps2 database, gets data, sends it back to end user, and goes back to inactive pool to be picked by next end user.
However, when job is done and Remedy session is back in pool, db link session to apps2 dbase remains open and connected forever.
Eventually as time goes on every single Remedy session from the pool will open db link to apps2 dbase. End result is 150 db link sessions in apps2 dbase doing nothing but using resources needed for apps2 itself.
The way Remedy operates is to pick existing Oracle session from the pool and use it for one off query. This session opens db link to apps2 database, gets data, sends it back to end user, and goes back to inactive pool to be picked by next end user.
However, when job is done and Remedy session is back in pool, db link session to apps2 dbase remains open and connected forever.
Eventually as time goes on every single Remedy session from the pool will open db link to apps2 dbase. End result is 150 db link sessions in apps2 dbase doing nothing but using resources needed for apps2 itself.
0