Re: managing open database links?

<stevedhoward_at_gmail.com> wrote in message news:1145391862.571176.28620_at_i39g2000cwa.googlegroups.com...
:I don't know of any way to do this in Oracle inherently, other than
: checking the MACHINE column in v$session. Not perfect, but one other
: way I can think of is to find the SQL being executed. It usually has
: something like A1.table_name in the statement.
:
: Also, if you are on Unix/Linux, the following just worked for me...
:
: ps -ef | grep $(lsof -i | grep your_remote_host | awk '{print $2}')
:
: That will produce a list of all connections originating from the remote
: database server. Ugly, but it may produce some better ideas.
:
: Of course, none of these ideas can be verified as database link
: connections, but just network and/or instance session connections.
:
: Regards,
:
: Steve
:

thanks; machine works -- but only if you know what machines you're looking for. anybody with TNS access could install XE or another desktop-suitable version of oracle and set up database links as well. and you can't just grab all machines but the host because there could be client-server sessions out there.

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 Received on Tue Apr 18 2006 - 17:10:25 CDT