[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'
