Re: managing open database links?

Mark C. Stock wrote:
>
> but i think the following will probably do it for all incoming db link
> connections:
>
> select *
> from v$session
> where upper(program) like 'ORACLE%'
> and username is not null
>
> the assumption is that all sessions for incoming db links will be from an
> oracle executable of some sort -- 'ORACLE.EXE' or something like
> 'oracle_at_somehostname (TNS V1-V3)'. so will the background processes, but the
> background processes do not have a username
>
> ++ mcs

That's very smart! You could also say type = 'USER' in place of username is not null, and possibly put @ in the like string 'ORACLE%@%'. For outgoing sessions, how about checking for DX locks?

select sid from v$lock where type = 'DX'

You see this whenever there's a distributed transaction lock, including a distributed query. It goes away when the session rollbacks or commits.

Yong Huang Received on Thu Apr 20 2006 - 13:13:17 CDT