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

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. 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.

  2. 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:
    ————

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569