managing open database links?

[Mark C. Stock]

<flame retardant>
i'm in the process of researching this but have not found anything yet, so
any pointers would be helpful
this is immediately applicable to R9.2
</flame retardant>

is there any way to detect all outgoing open database links (not just those
established by the current session)?

is there any way to detect all incoming open database links?

is there any direct or indirect way to close \outgoing or incoming database
links other than the outgoing links established by the current session?

++ mcs

[steved...@gmail.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

[steved...@gmail.com]

Here is another way...

localhost:oracle:localhost:/home/oracle>lsof -i | grep remotehost
oracle 22216 oracle 13u IPv4 809174380 TCP
localhost:56367->remotehost:2484 (ESTABLISHED)
localhost:oracle:localhost:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 18 16:27:29 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

SQL> select sid,serial#,schemaname,osuser from v$session where paddr
in(select addr from v$process where spid=22216);

SID SERIAL# SCHEMANAME OSUSER
---------- ---------- ------------------------------
------------------------------
525 302 HOWARDS oracle

Elapsed: 00:00:00.04
SQL>

This takes all outgoing connections to the remote host in which you are
interested, gets each PID, and checks for that PID in v$process. Since
that session is connected to the instance on the local host, you know
that it is a database link connection to the remote host (it could be a
JDBC connection from within the local instance well, I guess).

Regards,

Steve

[NetComrade]

We modified our application to do
select '<some_database_identifier>' from dual
on initial connection
to make our lives easier with identifying where really SQL is running
from

.......
We run Oracle 9.2.0.6 on RH4 AMD
we are currently looking for a DBA
remove NSPAM to email

[Mark C. Stock]

<steved...@gmail.com> wrote in message
news:1145391862....@i39g2000cwa.googlegroups.com...
:I don't know of any way to do this in Oracle inherently, other than

:

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@somehostname (TNS V1-V3)'. so will the background processes, but the
background processes do not have a username

++ mcs

[yon...@yahoo.com]

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@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

[Mark C. Stock]

<yon...@yahoo.com> wrote in message
news:1145556797.5...@v46g2000cwv.googlegroups.com...

:

i thought about the '@' but that does not show up for db link originating
from windows (specific chase is Oracle XE)

i'll take another look at v$lock -- i didn't think i saw anything there
before for a simple @dblink query

thanks.

++ mcs