Validba'S Home

本文标题:events: errorstack【本站如从外站转载,会注明出处】
本文链接:http://www.validba.net/2009/02/errorstack-%e8%af%8a%e6%96%ad%e4%ba%8b%e4%bb%b6/

errorstack是oracle提供的有一诊断事件, 可以用来跟踪某个ORA错误产生背后的具体信息, 比如常见的ORA-00942: table or view does not exist,利用此事件可以跟踪是乃条sql产生了这个错误。使用非常简单,设置该事件的用户必须具有alter  session权限,查看用户有乃些权限可以看这里.

[mayp@dbtest ~]$ sqlplus mayp/mayp

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 12 14:43:24 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set tracefile_identifier='errorstack';

Session altered.

SQL> alter session set events '942 trace name errorstack level 1';

Session altered.

SQL> select * from c;
select * from c
              *
ERROR at line 1:
ORA-00942: table or view does not exist

关闭事件跟踪:

SQL> alter session set events '942 trace name errorstack off';

Session altered.

在user_dump_dest目录查找包含’errorstack’的文件名’

[mayp@dbtest udump]$ ls -al|grep 'errorstack'
-rw-r-----  1 mayp oinstall 72344 Feb 12 14:53 mayp_ora_26232_errorstack.trc

或者我们可以检查alter.log文件的最后几行,也可以发现ora-00942错误.

mayp@dbtest bdump]$ tail -10 alert_mayp.log
Thread 1 advanced to log sequence 28 (LGWR switch)
  Current log# 1 seq# 28 mem# 0: /datavg/mayp/MAYP/onlinelog/o1_mf_1_4rnqvbxz_.log
  Current log# 1 seq# 28 mem# 1: /datavg/mayp/flash_recovery_area/MAYP/onlinelog/o1_mf_1_4rnqvdrg_.log
Thu Feb 12 07:00:55 2009
Thread 1 advanced to log sequence 29 (LGWR switch)
  Current log# 2 seq# 29 mem# 0: /datavg/mayp/MAYP/onlinelog/o1_mf_2_4rnqvgms_.log
  Current log# 2 seq# 29 mem# 1: /datavg/mayp/flash_recovery_area/MAYP/onlinelog/o1_mf_2_4rnqvjby_.log
Thu Feb 12 14:53:28 2009
Errors in file /oracle/product/admin/mayp/udump/mayp_ora_26232_errorstack.trc:
ORA-00942: table or view does not exist

错误信息的跟踪文件为:/oracle/product/admin/mayp/udump/mayp_ora_26232_errorstack.trc,找出是乃条语句引起了这个错误.

[mayp@dbtest udump]$ more /oracle/product/admin/mayp/udump/mayp_ora_26232_errorstack.trc
/oracle/product/admin/mayp/udump/mayp_ora_26232_errorstack.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2
System name:    Linux
Node name:      dbtest
Release:        2.6.9-42.0.0.0.1.EL
Version:        #1 Sun Oct 15 13:58:55 PDT 2006
Machine:        i686
Instance name: mayp
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 26232, image: oracle@dbtest (TNS V1-V3)

*** ACTION NAME:() 2009-02-12 14:53:28.772
*** MODULE NAME:(SQL*Plus) 2009-02-12 14:53:28.772
*** SERVICE NAME:(SYS$USERS) 2009-02-12 14:53:28.772
*** SESSION ID:(138.23881) 2009-02-12 14:53:28.772
*** 2009-02-12 14:53:28.772
ksedmp: internal or fatal error
ORA-00942: table or view does not exist
Current SQL statement for this session:
select * from c

select * from c 导致了ora-00942错误.

Relative Posts

• 2010/08/26 -- Tracking SQL Sort (0)
• 2009/02/11 -- events：10046 (0)