Error Stack

An error stack describes the current state of a process. It includes the current SQL statement and the process state for the process.

Oracle recommends taking an error stack dump to diagnose

what the process is doing
a problem identified by a systemstate dump
processes looping or hanging
Error stack dumps can also be triggered by an error

To dump an error stack use

    ALTER SESSION SET EVENTS
    'immediate trace name errorstack level level';

where level is one of the following

Level Description
0 Error stack only
1 Error stack and function call stack
2 As level 1 plus the process state
3 As level 2 plus the context area



The following ORADEBUG command has the same effect

    ORADEBUG DUMP ERRORSTACK level

An alternative syntax for the same command is

    ORADEBUG EVENT IMMEDIATE TRACE NAME ERRORSTACK level

To dump a level 3 errorstack when ORA-00942 (table or view does not exist) use

    ALTER SESSION SET EVENTS
    '942 trace name errorstack level 3';

A conditional errorstack dump can also be specified without a level e.g.

    ALTER SESSION SET EVENTS
    '604 trace name errorstack';

Alternatively a conditional errorstack dump can be specified in the init.ora file

    event = "942 trace name errorstack level 3"

Errorstacks can also be dumped conditionally using ORADEBUG

    ORADEBUG EVENT 942 TRACE NAME ERRORSTACK LEVEL 3

A level 3 errorstack contains the following sections

Call Stack Trace
Files Currently Opened
Process State
Pinned Buffer History
Cursor Dump
Fixed PGA
Fixed SGA
Fixed UGA
In memory trace dump

http://askdba.org/weblog/2008/04/errorstack-oracle-debugging-event/

 

跟蹤oracle特定報錯
errorstack

======================

不知道是由那個session觸發的錯誤的前提下，發現是什麼造成的這個錯誤
可以使用errorstack來跟蹤

啟動跟蹤：
例如，我不知道什麼時候那個sid會發生920錯誤。
alter system set events  '920 trace name errorstack level 3';

：
errorstack有4個級別：
0：只轉存錯誤堆疊資訊（逐漸被廢棄）
1：轉儲錯誤堆疊和函式呼叫堆疊
2：level 1+processstate
3：level 2+context area（顯示所有cursor，著重當前cursor）

開啟後會生成相應的trace檔案，在udump中
也可以通過指令碼獲取trace名，指令碼內容如下：

select
   d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name 
  from
     ( select p.spid
     from sys.v$mystat m,sys.v$session s,sys.v$process p
     where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
   ( select t.instance from sys.v$thread  t,sys.v$parameter  v 
      where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, 
   ( select value from sys.v$parameter where name = 'user_dump_dest') d
 /

TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/admin/bkdb/udump/bkdb_ora_30844.trc

分析trace檔案中有用資訊就可以進一步分析出現問題的原因

SELECT partno, sno, wono, stockdt
FROM stock2 a,stock3 b
WHERE a.partno = b.partno(+) and a.sno = b.sno(+) and a.wono = b.wono(+) and b.partno is null and b,sno is null and b.wono is null;

WHERE a.partno = b.partno(+) and a.sno = b.sno(+) and a.wono = b.wono(+) and b.partno is null and b,sno is null and b.wono is null
                                                                                                   *
ERROR at line 3:
ORA-00920: invalid relational operator

關閉跟蹤
alter system set events  '920 trace name errorstack off';

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-696624/，如需轉載，請註明出處，否則將追究法律責任。