Oracle提供接口用于诊断Oracle的错误信息。
诊断事件可以在Session级设置,也可以在系统级设置,通常假如要诊断全局错误,最好在系统级设置,以下是一个测试例子,所选事件只以示范为目的:
SQL alter system set event='984 trace name ERRORSTACK level10' scope=spfile;
System altered.
SQL startup force;
ORACLE instance started.
Total System Global Area101782828 bytes
Fixed Size 451884 bytes
Variable Size37748736 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
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 !
此时的984错误将会被跟踪,记录到跟踪文件中。
检查udump目录,找到trace文件:
[oracle@jumper oracle]$ cd $admin
[oracle@jumper udump]$ ls -sort
total 1020
4 -rw-r--r--1 oracle533 Mar2 16:06 t.sql
4 -rw-r--r--1 oracle522 Mar3 09:44 d.sql
20 -rw-r--r--1 oracle17445 Mar8 11:06 a.log
4 -rw-r-----1 oracle 3254 Mar 14 23:15 conner_ora_30683.trc
4 -rw-r-----1 oracle 1645 Mar 14 23:15 conner_ora_30701.trc
4 -rw-r-----1 oracle 1638 Mar 14 23:16 conner_ora_30719.trc
4 -rw-r-----1 oracle 1645 Mar 16 09:05 conner_ora_18565.trc
976 -rw-r-----1 oracle 993555 Mar 16 09:06 conner_ora_18589.trc
[oracle@jumper udump]$ vi conner_ora_18589.trc
/opt/oracle/admin/conner/udump/conner_ora_18589.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProdUCtion
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0
System name:Linux
Node name:jumper.hurray.com.cn
Release:2.4.21-15.EL
Version:#1 Thu Apr 22 00:27:41 EDT 2004
Machine:i686
Instance name: conner
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 18589, image: oracle@jumper.hurray.com.cn (TNS V1-V3)
*** 2005-03-16 09:06:56.178
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 -----
callingcall entryargument values in hex
location type point(? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+269 call ksedst()+0 0 ? 0 ? 0 ? 0 ? 922C89F ?
AA642A0 ?
ksddoa()+446 call ksedmp()+0 A ? AABDCA8 ? B70100B0 ?
3D8 ? 1 ? B7010114 ?
ksdpcg()+521 call ksddoa()+0 B70100B0 ? AABDCA8 ?
ksdpec()+220 call ksdpcg()+0 3D8 ? BFFF3D20 ? 1 ?
ksfpec()+133 call ksdpec()+0 3D8 ? 3D8 ? AABAE7C ?
BFFF3D54 ? 9835E89 ?
AA642A0 ?
[oracle@jumper udump]$
有了这个跟踪文件就轻易定位和诊断错误了。