Oracle SQL

    Tag Archives: dblink

    How to find out session info about session that comes from remote database through dblink

    Posted on July 25, 2014 by Sayan Malakshinov Posted in oracle, remote statements, SQL*Plus 6 Comments

    It is well known thing and you can even find it on MOS, but I have a little more simple script for it, so I want to show little example.

    First of all we need to start script on local database:

    SQL>                                                                                                                                                                   
    SQL> @transactions/global.sql
    Enter filters(empty for any)...
    Sid           :
    Globalid mask :
    Remote_db mask:
    
     INST_ID  SID    SERIAL# USERNAME REMOTE_DB REMOTE_DBID TRANS_ID         DIRECTION   GLOBALID                                           EVENT                      
    -------- ---- ---------- -------- --------- ----------- ---------------- ----------- -------------------------------------------------- ---------------------------
           1  275       4469 XTENDER  BAIKAL     1742630060 8.20.7119        FROM REMOTE 4241494B414C2E63616336656437362E382E32302E37313139 SQL*Net message from client
                                                                                                                                                                      
    

    Then we need to copy GLOBALID of interested session and run script on database that shown in column REMOTE_DBID, but with specifieng GLOBALID:

    SQL>                                                                                                                                                                                                 
    SQL> conn sys/syspass@baikal as sysdba
    Connected.
    
    ======================================================================
    =======  Connected to  SYS@BAIKAL(baikal)(BAIKAL)
    =======  SID           203
    =======  SERIAL#       38399
    =======  SPID          6536
    =======  DB_VERSION    11.2.0.4.0
    ======================================================================
    
    SQL> @transactions/global.sql
    Enter filters(empty for any)...
    Sid           :
    Globalid mask : 4241494B414C2E63616336656437362E382E32302E37313139
    Remote_db mask:
    
    INST_ID   SID    SERIAL# USERNAME  REMOTE_DB  REMOTE_DBID TRANS_ID   DIRECTION   GLOBALID                                            STATE                     
    ------- ----- ---------- --------- ---------- ----------- ---------- ----------- --------------------------------------------------  --------------------------
          1     9      39637 XTENDER   BAIKAL      1742630060 8.20.7119  TO REMOTE   4241494B414C2E63616336656437362E382E32302E37313139  [ORACLE COORDINATED]ACTIVE
    
    

    It’s quite simple and fast.

    dblink global transaction remote sessions

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL