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
Leave a Reply to admin Cancel reply