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

[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测试看看。