Finding open database links database wide

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

Other related posts: