RE: Problems in DB Link Connections.

Ganesh,

It worked with the following

execute dbms_session.close_database_link('testlink04');

though it is still weird that ALTER SESSION does not work for the same!

Thanks again!
Arul.

-----Original Message-----
From: Ganesh Raja [mailto:ganesh.raja_at_gmail.com] Sent: 05 November 2004 12:36
To: Kumar,A,Arul,XGF3C C
Cc: oracle-l_at_freelists.org
Subject: Re: Problems in DB Link Connections.

U can use Alter Session Close Database Link <dblink> to close inactive DB Links ..

~
Ganesh

On Fri, 5 Nov 2004 12:03:36 -0000, arul.kumar_at_bt.com <arul.kumar_at_bt.com> = wrote:
> Dear DBAs,
>=20
> We are facing lot of problems using db links across remote systems. I =
tried to simulate the same for better understanding. The facts are as = follows:
>=20
> Observations on a simulated environment
>=20
> Problem : The target remote systems complaining that we (source) are =
holding lots of INACTIVE connections which are not needed (at target = server!)
>=20
> Source - A
> Target - B
>=20
> Db Link - TestLink04
>=20
> 1. A database connection will be created at the target whenever source =
uses a db link.
>=20
> 2. The status of the above connection will be shown as ACTIVE only =
when it is REALLY
> doing some massive work. ASAP, it changes the status to INACTIVE
>=20
> The above is one of the reasons why MOST of the connections are being =
shown as INACTIVE, though ACTUALLY they are in use.
>=20
> For example : Firing a SELECT * FROM <mailto:Big_Table_at_target> =
Big_Table_at_target IS changing the status of the session at target from = INACTIVE to
> ACTIVE MANY a times in quick succession though at source, STILL the =
records are getting displayed.
>=20
> 3. Though the source is finished with its work with the db link (by =
properly issuing a COMMIT / ROLLBACK), the connection
> at the target will be still present (as INACTIVE, ofcourse).
>=20
> 4. The target database connection will ONLY terminate when the source =
connection terminates (which NEVER happens in
> OSD I believe). So, the connection once established at the target will =
LIVE for ever if it is not
> killed forcibly!
>=20
> 5. If the target INACTIVE connection is killed, then at source, any =
further queries to the DBLINK connection may throw
>=20
> ORA-02068 : following severe error from testlink04 ORA-01012: not =
logged on
>=20
> at the first invocation of any SQL at target through the same session =
at source. Subsequent calls will automatically
> trigger a new connection at target.
>=20
> Any suggestions for reducing the number of INACTIVE connections at the =
target will be appreciated.
>=20
> Many Thanks,
> Arul.
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l