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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-696624/