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
ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SAVEPOINTS LEVEL 1’ Raises ORA-03113/ORA-07445[SIGSEGV]/[STRLEN] Errors
Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.6
This problem can occur on any platform.
Symptoms
The following errors have been observed for this issue:-
ORA-03113 : From SQLPLUS session
ORA-07445: exception encountered: [SIGSEGV] [Address not mapped to object] : From alert log
ORA-07445[STRLEN] : From alert log
All of the following symptoms will be observed :-
a) Using ALTER SESSION as below
ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SAVEPOINTS LEVEL 1’;
This will raise ORA-03113 from the SQLPLUS session
b) At same timestamp either ORA-07445[SIGSEGV] or 7445[STRLEN] will be reported in the alert log
The stack will be similar to:-
ksedmp ssexhd sigacthandler xctdsp ksddoa ksdsvg ksdsev ksdacn ksdprs kkyase opiexe opiall0 kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o main start
c) ALTER SESSION is fine if a SAVEPOINT is created first
Cause
BUG:4059943
Solution
Ensure a savepoint is created before the ALTER SESSION
Test1
——–
SQL> connect scott/tiger
SQL> alter session set events ‘immediate trace name savepoints level 1’
==> ora-3113 + ora-7445
Test2
——–
SQL> connect scott/tiger
SQL> savepoint scott1;
SQL> alter session set events ‘immediate trace name savepoints level 1’
Session altered.
Whilst an internal error should not be raised BUG:4059943 is fixed in 10gR2 and the fix is to report the ‘Session Altered’ but to generate a trace to UDUMP to report :-
NO SAVEPOINT FOR CURRENT PROCESS
The bug can be monitored but it is unlikely a fix wlll be made into earlier releases as a valid workaround is available.
Hdr: 4059943 9.2.0.5.0 RDBMS 9.2.0.5.0 TXN MGMT LOCAL PRODID-5 PORTID-212 ORA-7445
Abstract: ALTER SESSION EVENT SAVEPOINTS LEADS TO ORA-7445 [STRLEN] WHEN NO SAVEPNT IS SET
PROBLEM:
——–
Provide the following:
1. Clear description of the problem encountered
ora-7445 [strlen] occurs when performing:
alter session set events ‘immediate trace name savepoints level 1’;
2. Pertinent configuration information (MTS/OPS/distributed/etc)
3. Indication of the frequency and predictability of the problem
Reproducable at will
4. Sequence of events leading to the problem
Performing above statement in a session where not yet a savepoint has been
set
5. Technical impact on the customer. Include persistent after effects.
None
DIAGNOSTIC ANALYSIS:
——————–
the alter session in question perfectly reproduces the ora-7445 and works
fine after a savepoint has been set:
SQL> connect scott/tiger
SQL> alter session set events ‘immediate trace name savepoints level 1’
==> ora-3113 + ora-7445
SQL> connect scott/tiger
SQL> savepoint scott1;
SQL> alter session set events ‘immediate trace name savepoints level 1’
Session altered.
WORKAROUND:
———–
The WorkAround in this case is to perform the alter session after setting a
savepoing
RELATED BUGS:
————-
I could not find a bug covering this issue
REPRODUCIBILITY:
—————-
Problem is reproducable at will:
212 AIX 5l – Oracle 9.2.0.5.0
453 Sun/Solaris – Oracle 9.2.0.6.0
23 Sun/Solaris – Oracle 10.1.0.3.0
TEST CASE:
———-
Testcase can be read above
STACK TRACE:
————
bcabn1_ora_35348.trc
*** ID:(18.346) 2004-12-09 14:13:42.051
====================================================
SAVEPOINT FOR CURRENT PROCESS
——————————
flag: 0x0
*** 14:13:42.053
Exception signal: 11 0x90000000001d0bc (strlen+003c) e92c0009
—– Call Stack Trace —–
strlen vsnprintf ksdfmw ksdwrf xctdsp ksddoa ksdsvg ksdsev ksdacn ksdprs
kkyase opiexe opiall0 kpoal8 opiodr ttcpip opitsk opiino
..
*** 14:13:42.123
ksedmp: internal or fatal error
ORA-7445: exception encountered: core dump [] [] [] [] [] []
Current SQL statement for this session:
alter session set events ‘immediate trace name savepoints level 1’
—– Call Stack Trace —–
ksedmp ssexhd vsnprintf ksdfmw ksdwrf xctdsp ksddoa ksdsvg ksdsev ksdacn
ksdprs kkyase opiexe opiall0 kpoal8 opiodr ttcpip
SUPPORTING INFORMATION:
———————–
The following files will be uploaded:
– alert_bcabn1.log.Z
– bcabn1_ora_35348.trc.Z
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-
DIAL-IN INFORMATION:
——————–
IMPACT DATE:
————