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

最新推荐文章于 2025-09-13 19:07:06 发布

weixin_33713707

最新推荐文章于 2025-09-13 19:07:06 发布

阅读量71

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/605742

本文介绍如何在Oracle数据库中使用特定命令追踪当前进程中的Savepoint信息。通过实例演示了如何设置Savepoint，并利用oradebug命令进行即时追踪，查看不同级别的Savepoint详细情况。

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