From oracle-l-bounce@freelists.org Fri Feb 11 00:42:14 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1B6gEXa029093 for ; Fri, 11 Feb 2005 00:42:14 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j1B6gEem029085 for ; Fri, 11 Feb 2005 00:42:14 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 563896FF93; Fri, 11 Feb 2005 00:41:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02817-10; Fri, 11 Feb 2005 00:41:09 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D1BA56FF70; Fri, 11 Feb 2005 00:41:08 -0500 (EST) content-class: urn:content-classes:message Subject: Finding open database links database wide MIME-Version: 1.0 Priority: normal Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Date: Fri, 11 Feb 2005 16:38:28 +1100 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: replace or translate? Thread-Index: AcUPk9f0JhuNabJ0S8OUIKfMq/5OcwAZq2nA From: "Reardon, Bruce (CALBBAY)" To: X-OriginalArrivalTime: 11 Feb 2005 05:38:29.0128 (UTC) FILETIME=[EA414080:01C50FFB] X-archive-position: 16065 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Bruce.Reardon@comalco.riotinto.com.au Precedence: normal Reply-To: Bruce.Reardon@comalco.riotinto.com.au X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.1 required=5.0 tests=LINES_OF_YELLING, LINES_OF_YELLING_2 autolearn=no version=2.60 X-Spam-Level: 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