[20241127]如何知道事务是包含dblink的情况.txt

--//工作需要，我需要知道某个事务里面是否涉及dblink，当简单的sql语句包含dblink时，会同步scn，导致产生小量日志。
--//我需要知道某个事务是否涉及dblink，测试看看。

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

create public database link "loopback"
connect to scott
identified by "book"
using '127.0.0.1/book01p';
--//没有其他主机，使用loopback地址。

2.测试：
--//session 1:
SCOTT@book01p> create table deptx as select * from dept;
Table created.

SCOTT@book01p> select * from deptx@loopback where deptno=20;
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        20 RESEARCH                       DALLAS

--//session 2:
SYS@book01p> @ trans
SYS@book01p> @ pr
==============================
SID                           : 22
SERIAL#                       : 33275
TRANS_USERNAME                : SCOTT
PROGRAM                       : SQLPLUS@CENTTEST (TNS V1-V3)
TADDR                         : 0000000077170488
SES_ADDR                      : 000000007BC6A1C8
USED_UBLK                     : 1
USED_UREC                     : 1
0xFLAG                        :    421603
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STATUS                        : ACTIVE
STATUS2                       : ACTIVE
START_DATE                    : 2024-11-27 16:30:34
XIDUSN                        : 1
XIDSLOT                       : 13
XIDSQN                        : 1978
XID                           : 01000D00BA070000
PRV_XID                       : 0000000000000000
PTX_XID                       : 0000000000000000
PL/SQL procedure successfully completed.
--//0xFLAG =  421603,感觉从这个标识确定是否包含dblink。

--//session 1:
SCOTT@book01p> commit;
Commit complete.

SCOTT@book01p> update deptx set LOC=lower(loc) where deptno=20;
1 row updated.

--//session 2:
SYS@book01p> @ trans
SYS@book01p> @ pr
==============================
SID                           : 22
SERIAL#                       : 33275
TRANS_USERNAME                : SCOTT
PROGRAM                       : SQLPLUS@CENTTEST (TNS V1-V3)
TADDR                         : 0000000077170488
SES_ADDR                      : 000000007BC6A1C8
USED_UBLK                     : 1
USED_UREC                     : 1
0xFLAG                        :      1E03
STATUS                        : ACTIVE
STATUS2                       : ACTIVE
START_DATE                    : 2024-11-27 16:33:58
XIDUSN                        : 7
XIDSLOT                       : 8
XIDSQN                        : 1989
XID                           : 07000800C5070000
PRV_XID                       : 0000000000000000
PTX_XID                       : 0000000000000000
PL/SQL procedure successfully completed.
--//0xFLAG = 1E03, 是否前面的42可以作为dblink事务标识。

--//session 1:
SCOTT@book01p> select * from deptx@loopback where deptno=20;
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        20 RESEARCH                       dallas

--//session 2:
SYS@book01p> @ pr
==============================
SID                           : 22
SERIAL#                       : 33275
TRANS_USERNAME                : SCOTT
PROGRAM                       : SQLPLUS@CENTTEST (TNS V1-V3)
TADDR                         : 0000000077170488
SES_ADDR                      : 000000007BC6A1C8
USED_UBLK                     : 1
USED_UREC                     : 1
0xFLAG                        :    421E03
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STATUS                        : ACTIVE
STATUS2                       : ACTIVE
START_DATE                    : 2024-11-27 16:33:58
XIDUSN                        : 7
XIDSLOT                       : 8
XIDSQN                        : 1989
XID                           : 07000800C5070000
PRV_XID                       : 0000000000000000
PTX_XID                       : 0000000000000000
PL/SQL procedure successfully completed.
--//视乎前面42表示dblink事务。

--//简单测试一下，感觉情况就是这样，0xFLAG的前面42标识就是dblink相关事务。
--//有空在11g测试看看。