Finding open database links database wide

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Fri, 11 Feb 2005 16:38:28 +1100
Message-ID: <E10A27083F21674ABFAF6CD3D0BAC2F9017BFF@calbbsv025.cal.riotinto.org>

How can I find which sessions have open database links?

I know v$dblink will show me if my own session has a database link open. And http://www.jlcomp.demon.co.uk/faq/find_dist.html shows how to find = distributed transactions that have not been committed / rolled back, but = won't show sessions that still have link open (eg not done alter session = close database link blah) but have done a rollback / commit.

This is particularly for finding who owns sessions on the external = database when it is connected to via HSODBC and not an Oracle database.

Thanks,
Bruce Reardon

eg:
-- session 1 - no links open to start off with
16:30:28 SQL> select * from v$dblink;
no rows selected

16:30:31 SQL> select count(*) from the_tab_at_remotedb; COUNT(*)

217169
1 row selected.

16:30:46 SQL> select * from v$dblink;

DB_LINK                OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD =

COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- =

REMTOEDB.WORLD                20 YES NO  V7                0 YES NO      =
                0

1 row selected.

16:30:52 SQL>=20

session 2 16:30:58 SQL> select sid from v$session s where s.saddr in ( select = x.k2gtdses from sys.x$k2gte x );
back to session 1 16:32:04 SQL> roll; Rollback complete.
back to session 2 16:31:23 SQL> select sid from v$session s where s.saddr in ( select = x.k2gtdses from sys.x$k2gte x );
back to session 1 - the link is still open 16:32:24 SQL> select * from v$dblink;

DB_LINK                OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD =

COMMIT_POINT_STRENGTH
-------------------- ---------- --- --- ------ ------------ --- --- =

REMOTEDB.WORLD                20 YES NO  V7                0 NO  NO      =
                0

1 row selected.

16:32:27 SQL> alter session close database link remotedb;

Session altered.

16:32:32 SQL> select * from v$dblink;

no rows selected

16:32:36 SQL>=20 Thanks,
Bruce Reardon

NOTICE
This e-mail and any attachments are private and confidential and may = contain privileged information. If you are not an authorised recipient, = the copying or distribution of this e-mail and any attachments is = prohibited and you must not read, print or act in reliance on this = e-mail or attachments.
This notice should not be removed.
--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 11 2005 - 00:42:14 CST