在使用Oracle資料庫的過程中，可能會遇到各種各樣的錯誤或異常，很多異常的提示並不具體，我們有必要了解一下Oracle的ErrorStack跟蹤方式。

ErrorStack是Oracle提供的一種對於錯誤堆疊進行跟蹤的方法，通過設定跟蹤可以將一些錯誤的後臺資訊詳盡地轉儲出來，寫入跟蹤檔案，對於錯誤的研究與診斷非常有效。

設定ErrorStack主要有4個級別：

0 僅轉儲錯誤堆疊（0級已經被逐漸廢棄）

1 轉儲錯誤堆疊和函式呼叫堆疊

2 Level1 + ProcessState

3 Level2 + Context area（顯示所有cursors，著重顯示當前cursor）

ErrorStack可以在例項級或會話級別設定，也可以在引數檔案中設定，這個設定僅當某個特定的錯誤出現時才被觸發，如設定ORA-00942事件的跟蹤：

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

一個客戶曾經出現如下ORA-01438錯誤，提示資料的精度超過允許值，是後臺JOB排程的任務：

Mon Jul 13 10:27:312009
Errors in file /admin/erpdb/bdump/erpdb1_j000_447020.trc:
ORA-12012: error on auto execute of job 22
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at "ERP.TIMRDU", line 13
ORA-06512: at line 1

跟蹤檔案中預設的不會記錄具體的SQL、繫結變數等資訊，我們可以通過ErrorStack進行後臺跟蹤，獲得更詳細的資訊，執行如下程式碼中的SQL：

alter system set events='1438 trace name errorstack forever,level 3';

然後可以手工執行出錯的儲存過程（程式碼如下），獲得跟蹤檔案，再關閉跟蹤：

alter system set events='1438 trace name errorstack off';

在Oracle 10g中，這樣的操作會被記錄到日誌檔案中：

Mon Jul 13 10:48:392009
OS Pid: 541528 executed alter system set events '1438 trace name Errorstackforever,level 3'
Mon Jul 13 10:56:06 2009
Errors in file /admin/erpdb/udump/erpdb1_ora_267056.trc:
ORA-01438: value larger than specified precision allowed for this column
Mon Jul 13 10:56:08 2009
Trace dumping is performing id=[cdmp_20090713105608]
Mon Jul 13 10:57:15 2009
OS Pid: 541528 executed alter system set events '1438 trace name Errorstackoff'

接下來分析獲得的跟蹤檔案，就可以獲得SQL文字線索，找到根本問題。在這個案例中，我們得到的跟蹤檔案，其關鍵SQL內容如下，通過這個SQL對照資料表很快就找到了精度超過的Number型欄位：

***SESSION ID:(857.16304) 2009-07-13 10:56:06.429
*** 2009-07-13 10:56:06.429
ksedmp: internal or fatal error
ORA-01438: value larger than specified precision allowed for this column
Current SQL statement for this session:
INSERT INTO CONTRAPAYM (IHCODE,GTICODE,IDX,HCODE,PORDATE,FCODE,FCY,CCODE,ECODE,FLAG,MATCHFLAG,BCODE,MONTHZL,STATUS,AUTOFLAG ,REMARK) SELECTDISTINCT IHCODE,'0000000000000000' GTICODE,(IDX+100) IDX,HCODE,:B3 PORDATE,FCODE,:B2FCY,CCODE,ECODE, FLAG,0 MATCHFLAG,BCODE,MONTHZL,STATUS,4 AUTOFLAG ,'Proc_AdjustContractpayment' REMARK FROM CONTRAPAYM WHERE IHCODE=:B1 ANDIDX=(SELECT MAX(IDX) FROM CONTRACTPAYMENT WHERE IHCODE=:B1 ) AND ROWNUM=1
----- PL/SQL Call Stack -----
object line object
handle number name
7000002ca366e80 100 procedure ERP.PROC_ADJCONTRAPAYM
700000336a1a070 236 procedure ERP.PROC_AUTOBATPROC
7000002ca367df0 5 procedure ERP.TIMRDU
700000342eb7c20 1 anonymous block

在跟蹤檔案中，還有大量的堆疊資訊，對於複雜的問題，還可以通過進一步細緻的堆疊分析進行深入追蹤。

可以很容易地測試這一功能的使用，比如使用如下程式碼中的測試過程：

SQL> alter system set events '984 trace name errorstack off';
SQL> connect eygle/eygle
SQL> create table t (name varchar2(10),id number);

Table created.
SQL> insert into t values(a,1);
insert into t values(a,1)
*
ERROR at line 1:
ORA-00984: column not allowed here
SQL> alter system set events '984 trace name errorstack off';

在告警日誌檔案中就可以獲得如下資訊：

Mon Jul 13 22:55:592009
OS Pid: 2431 executed alter system set events '984 trace name ERRORSTACK level3'
Mon Jul 13 22:59:12 2009
Errors in file /opt/oracle/admin/mmstest/udump/mmstest_ora_2520.trc:
ORA-00984: column not allowed here
Mon Jul 13 23:01:01 2009
OS Pid: 2431 executed alter system set events '984 trace name errorstack off'

獲得的跟蹤檔案裡記錄了insert的相關資訊：

*** 2009-07-13 22:59:12.928
ksedmp: internal or fatal error
ORA-00984: column not allowed here
Current SQL statement for this session:
insert into t values(a,1)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (?means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+27 all ksedst1() 0? 1 ?

---

---

以下引用在ITPUB上的一個討論，是使用ErrorStack跟蹤解決問題的典型案例（原文連結指向ITPUB）：

問題概述

在進行資料庫例行維護時，遇到資料庫正常匯出，但匯入時遇到有張表不能匯入的問題。這張表儲存工藝路線，大約共有160萬條記錄。表的不能匯入影響一部分功能的實現，各單位反映強烈。

初步分析

首先排除是系統bug所帶來的問題，因為前期匯入匯出未發現問題；另外由於是對同一資料庫進行的匯入匯出，也排除了字符集問題。

單獨再次匯入此表，出現錯誤如下；

[oracle@zhx25 ~]$ imp system/ceshi fromuser=dba_mgr touser=dba_mgr tables=t_routdef ignore=y file=1.dmp

Import: Release 10.2.0.1.0 - Production on Tue Mar 18 14:19:49 2008

Copyright (c) 1982, 2005, Oracle.All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and ZHS16GBK NCHAR character set

. importing DBA_MGR's objects into DBA_MGR

. . importing table "T_ROUTDEF"

IMP-00058: ORACLE error 1438 encountered

ORA-01438: value larger than specified precision allowed for this column

IMP-00028: partial import of previous table rolled back: 20501 rows rolled back

解決方向：有可能是資料中存在問題，很有可能是輸入一些特殊字元，這些字元和ORACLE自身使用的萬用字元一致，所以導致系統不能識別。

問題解決

1、對問題進行跟蹤

對系統設定1438 errorstack跟蹤：

SQL> alter system set events='1438 trace name Errorstack forever,level 10';

System altered

然後重新執行IMP；

2、檢視UDUMP下日誌,如下:

/opt/oracle/admin/orcl/udump/orcl_ora_32355.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /opt/oracle/product/10.2.0

System name:Linux

Node name:zhx25.cccc.com

Release:2.6.9-22.ELsmp

Version:#1 SMP Mon Sep 19 18:00:54 EDT 2005

Machine:x86_64

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 17

Unix process pid: 32355, image: oracle@zhx25.cccc.com (TNS V1-V3)

*** SERVICE NAMESYS$USERS) 2008-03-18 17:11:02.014

*** SESSION ID135.949) 2008-03-18 17:11:02.014

*** 2008-03-18 17:11:02.014

ksedmp: internal or fatal error

ORA-01438: value larger than specified precision allowed for this column

Current SQL statement for this session:

INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "T_ROUTDEF" ("PRTNO", "SEQNO", "SEQDESC", "PRTEFLAG", "SEQCC", "SEQNXTNO", "SEQDEPT", "WKCCODE", "WKCALTCODE", "SEQSETHR", "LAB_SEQSETHR", "SEQRUNHR", "LAB_SEQRUNHR", "SEQQUEHR", "LAB_SEQQUEHR", "SEQTRANHR", "LAB_SEQTRANHR", "SEQNOQLTRAT", "SEQCHGDAT", "SEQCHGRSN", "SEQCHGCTLR", "MACHCODE", "MACHALTCODE", "RESCODE", "KEYSEQ", "SEQCRYLOT", "INITOVERRATE", "COMWORK", "SEQLOTFLAG", "SEQEFDAT", "SEQIEFDAT", "SEQEFLOTNO", "SEQIEFLOTNO", "SEQECONO", "PRTSEQCST", "SEQSELFCST", "INVYORN", "SEQQTY", "SEQNOTE", "NOPRTAVELZ", "OUTSEQFLAG", "WXCST", "SEQGSCHGCTLR", "SEQGSCHGDAT", "SEQSHCTL", "SEQSHDAT", "SEQSHSIGN", "PRICE", "ACCNO", "ROUSTAT", "FIXMFLAG", "BANCL" VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52)

----- Call Stack Trace -----

calling

call

entry

argument values in hex 

location

type

point

(? means dubious value)

…………

oacdef = 6a9477a0

Offsi = 48, Offsi = 1440

Opcode = 2

Bind Twotask Scalar Sql In (may be out) Copy 

oacdef = 6a9477d0

Offsi = 48, Offsi = 1472

Opcode = 2

Bind Twotask Scalar Sql In (may be out) Copy 

oacdef = 6a947800

Offsi = 48, Offsi = 1504

Opcode = 2

Bind Twotask Scalar Sql In (may be out) Copy 

oacdef = 6a947830

Offsi = 48, Offsi = 1536

Opcode = 2

Bind Twotask Scalar Sql In (may be out) Copy 

oacdef = 6a947860

Offsi = 48, Offsi = 1568

Opcode = 2

Bind Twotask Scalar Sql In (may be out) Copy 

oacdef = 6a947890

Offsi = 48, Offsi = 1600

Opcode = 2

Bind Twotask Scalar Sql In (may be out) Copy 

oacdef = 6a9478c0

Offsi = 48, Offsi = 1632

kkscoacd

Bind#0

oacdty=01 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=852 siz=3344 off=0

kxsbbbfp=2a974dc398

bln=32

avl=23

flg=05

value="?

Bind#1

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=32

kxsbbbfp=2a974dc3b8

bln=22 avl=22 flg=01

value=###

An invalid number has been seen.Memory contents are :

Dump of memory from 0x0000002A974CDE88 to 0x0000002A974CDE9E

2A974CDE80 3331312E 0406C102 [.113....]

2A974CDE90 C9BBC6C8 52024D01 30310248 00003304[.....M.RH.10.3..]

Bind#2

oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=56

kxsbbbfp=2a974dc3d0

bln=32

avl=00

flg=01

Bind#3

oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=88

kxsbbbfp=2a974dc3f0

bln=32

avl=01

flg=01

value="M"

…………

從跟蹤檔案可以看到，問題是系統認為地址為2A974CDE88的數值非法，在匯入會提示ORA-1438錯誤。本值在資料庫中定義為number(8)型別，但從結果來看，系統把這個值當成了number(24)型別，並且由於格式不對，不能取出值。

3、找到DMP中相關資料

用winhex開啟dmp檔案，搜尋十六進位制串“2E313133 02C10604 C8C6BBC9 014D0252 48023130 0433F3FF”（對trace檔案中的值要進行順序調整）

4、對匯出資料進行更改

由於“2E313133 02C10604 C8C6BBC9 014D0252 48023130 0433F3FF”不是一個有效的number型別，需要對它進行調整。調整如下，只要確保調整的值是number型別的即可。

5、對資料庫相關資料項的型別進行調整

把資料項的型別由number(*)改為number(38)；

6、重新進行資料匯入

[oracle@zhx25 ~]$ imp system/ceshi fromuser=dba_mgr touser=dba_mgr tables=t_routdef ignore=y file=1.dmp

Import: Release 10.2.0.1.0 - Production on Tue Mar 18 14:19:49 2008

Copyright (c) 1982, 2005, Oracle.All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and ZHS16GBK NCHAR character set

. importing DBA_MGR's objects into DBA_MGR

. . importing table

"T_ROUTDEF"

1646404

本文通過介紹ErrorStack的應用，將這一常用的診斷工具介紹給大家，作為DBA，在遇到複雜問題時，通過這些手段深入分析，不僅有助於問題的解決，還將完善自己的知識體系，祝大家學習能夠不斷進步、深入。