Oracle中如何追踪savepoint

Oracle中的savepoint是事务中的标示符，以帮助我们做到部分操作的回滚。 我们可以通过trace找出会话中自上次commit以来所有的savepoint保存点，要阅读这些trace内容你需要有user_dump_dest目录的相关权限。

SQL> oradebug setmypid; 已处理的语句 SQL> SQL> SQL> insert into  system.linktest values(2); 已创建 1 行。 SQL> savepoint a; 保存点已创建。 SQL> insert into system.linktest values(3); 已创建 1 行。 SQL> savepoint b; 保存点已创建。 SQL> insert into system.linktest values(4); 已创建 1 行。 SQL> savepoint d; 保存点已创建。 SQL> oradebug event immediate trace name savepoints level 1；

已处理的语句 SQL> oradebug tracefile_name; e:\oracle\product\10.2.0\admin\orcln\udump\orcln_ora_1756.trc

其中 oradebug setmypid;  表示要trace的session为当前会话。

在非mts环境下，我们也可以通过如：

oradebug setospid OSPID;         -- 通过指定OS中的进程号,即v$process中的spid

oradebug setorapid ORAPID;      -- 通过指定Oracle 中的进程号，即v$process中的pid

来定制我们需要trace的相关session。

退回到命令行格式，该trace主要内容如下：

*** 2009-09-15 12:39:37.828 *** SERVICE NAME:(SYS$USERS) 2009-09-15 12:39:37.828 *** SESSION ID:(151.83) 2009-09-15 12:39:37.828 ==================================================== SAVEPOINT FOR CURRENT PROCESS ------------------------------ flag: 0x1 name: D dba: 0x800084, sequence #: 177, record #: 51, savepoint #: 81 status: VALID, next: 28236068 name: B dba: 0x800084, sequence #: 177, record #: 50, savepoint #: 66 status: VALID, next: 282373F4 name: A dba: 0x800084, sequence #: 177, record #: 49, savepoint #: 52 status: VALID, next: 00000000

metalink中的相关介绍如下：

Subject:	How To Find Out The Savepoint For Current Process
	Doc ID:	108611.1	Type:	BULLETIN
	Modified Date:	16-JUN-2004	Status:	PUBLISHED

PURPOSE ------- This information shows you how to get the savepoints you have issued since the last commit was issued. SCOPE & APPLICATION ------------------- Any user can follow the steps to find out the savepoints in their own session.  However, only the the user who have read permission to the user_dump_dest directory can view the output. Steps to Retrieve the Savepoint Issued after the Last Commit ------------------------------------------------------------ In the following illustration, two savepoints are created in the same session.  Then a savepoints dump is issued to get the savepoint information.  An example of the dump file is included. SQL> insert into emp (empno, ename) 2  values (9995, 'vso'); 1 row created. SQL> savepoint pt1; Savepoint created. SQL> insert into emp (empno, ename) 2  values (9994, 'vso'); 1 row created. SQL> savepoint pt2; Savepoint created. SQL> alter session set events 2  'immediate trace name savepoints level 1'; Session altered. A trace file is generated in the user_dump_directory.  The content of the trace file is included in the following: Dump file /u04/app/oracle/admin/R805/udump/r805_ora_18763.trc Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production With the Partitioning and Objects options PL/SQL Release 8.0.5.2.0 - Production ORACLE_HOME = /u04/app/oracle/product/8.0.5 System name:    SunOS Node name:      rtcsol1 Release:        5.6 Version:        Generic_105181-17 Machine:        sun4u Instance name: R805 Redo thread mounted by this instance: 1 Oracle process number: 8 Unix process pid: 18763, image: oracleR805 *** 2000.04.18.14.11.37.000 *** SESSION ID:(7.391) 2000.04.18.14.11.37.000 ==================================================== SAVEPOINT FOR CURRENT PROCESS ------------------------------ flag: 0x1 name: PT2 dba: 0x80020e, sequence #: 0, record #: 9, savepoint #: 131 status: VALID, next: 10a0868 name: PT1 dba: 0x80020e, sequence #: 0, record #: 7, savepoint #: 109 status: VALID, next: 0