[20180222]Oracle中如何追蹤savepoint.txt

[20180222]Oracle中如何追蹤savepoint.txt

--//測試如何跟蹤事務的savepoint點.測試參考http://www.askmaclean.com/archives/how-to-find-out-the-savepoint-for-current-process.html

1.環境:

SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:

SCOTT@book> @ &r/spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        80         59 34661                    DEDICATED 34662       30         24 alter system kill session '80,59' immediate;

SCOTT@test01p> create table t ( a number);
Table created.      

insert into t values(1);
savepoint a;
insert into t values(2);
savepoint b;
insert into t values(3);
savepoint c;

--//開啟新的session,以sys使用者執行:
SYS@book> oradebug setospid 34662
Oracle pid: 30, Unix process pid: 34662, image: oracle@gxqyydg4 (TNS V1-V3)

SYS@book> oradebug event immediate trace name savepoints level 1;
Statement processed.

SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_34662.trc

--//檢查轉儲檔案內容:
Received ORADEBUG command (#1) 'event immediate trace name savepoints level 1' from process 'Unix process pid: 34785, image: <none>'
====================================================
SAVEPOINT FOR CURRENT PROCESS
------------------------------
   flag: 0x3
    xctsp name:C
          svpt(xcb:0x81bc9708 sptn:0xfc46 uba: 0x00c00120.03e9.28)
          status:VALID next:0x7d5e61e0
    xctsp name:B
          svpt(xcb:0x81bc9708 sptn:0xfc36 uba: 0x00c00120.03e9.27)
          status:VALID next:0x7cdebbf0
    xctsp name:A
          svpt(xcb:0x81bc9708 sptn:0xfc26 uba: 0x00c00120.03e9.26)
          status:VALID next:(nil)

*** 2018-02-22 10:13:55.459
Finished processing ORADEBUG command (#1) 'event immediate trace name savepoints level 1'

--//再次執行:
SCOTT@test01p> savepoint c;
Savepoint created.

*** 2018-02-22 10:15:33.953
Received ORADEBUG command (#3) 'event immediate trace name savepoints level 1' from process 'Unix process pid: 34785, image: <none>'
====================================================
SAVEPOINT FOR CURRENT PROCESS
------------------------------
   flag: 0x3
    xctsp name:C
          svpt(xcb:0x81bc9708 sptn:0xfc4b uba: 0x00c00120.03e9.28)
          status:VALID next:0x7d5e61e0
    xctsp name:B
          svpt(xcb:0x81bc9708 sptn:0xfc36 uba: 0x00c00120.03e9.27)
          status:VALID next:0x7cdebbf0
    xctsp name:A
          svpt(xcb:0x81bc9708 sptn:0xfc26 uba: 0x00c00120.03e9.26)
          status:VALID next:(nil)

*** 2018-02-22 10:15:33.954
Finished processing ORADEBUG command (#3) 'event immediate trace name savepoints level 1'
--//依舊3個.做一個回滾操作看看.

SCOTT@book> rollback to b;
Rollback complete.

Received ORADEBUG command (#4) 'event immediate trace name savepoints level 1' from process 'Unix process pid: 34785, image: <none>'
====================================================
SAVEPOINT FOR CURRENT PROCESS
------------------------------
   flag: 0x3
    xctsp name:B
          svpt(xcb:0x81bc9708 sptn:0xfc36 uba: 0x00c00120.03e9.27)
          status:VALID next:0x7cdebbf0
    xctsp name:A
          svpt(xcb:0x81bc9708 sptn:0xfc26 uba: 0x00c00120.03e9.26)
          status:VALID next:(nil)

*** 2018-02-22 10:16:18.705
Finished processing ORADEBUG command (#4) 'event immediate trace name savepoints level 1'

--//僅僅剩下2個.

SYS@book> oradebug event immediate trace name savepoints level 12;
Statement processed.

*** 2018-02-22 10:16:58.480
Received ORADEBUG command (#5) 'event immediate trace name savepoints level 12' from process 'Unix process pid: 34785, image: <none>'
====================================================
SAVEPOINT FOR CURRENT PROCESS
------------------------------
   flag: 0x3
    xctsp name:B
          svpt(xcb:0x81bc9708 sptn:0xfc36 uba: 0x00c00120.03e9.27)
          status:VALID next:0x7cdebbf0
    xctsp name:A
          svpt(xcb:0x81bc9708 sptn:0xfc26 uba: 0x00c00120.03e9.26)
          status:VALID next:(nil)

*** 2018-02-22 10:16:58.481
Finished processing ORADEBUG command (#5) 'event immediate trace name savepoints level 12'