AskDba.org Weblog

One more post arising out of my frequent visits to Oracle forum Basically there was a question on “How to set events for ORA – 1652 errors”

I had replied to the post but Aman asked me if I could provide any notes regarding Errorstack.I didn’t had one so I informed him that I will be posting on my blog ( Cheap Publicity of my blog )

Ok, this was the background to it, now coming to the main thing. Errorstack is used as debugging event for following reasons

• To extract Failing SQL statement
• To get Oracle Function call (to be used by Oracle) for interpreting cause for the error (e.g ORA 4030, ORA- 4031). It is also used in case of database hang scenarios. Refer to my earlier article on database hang
• It also contains execution plan in some cases which can be used to diagnose ORA – 1652 errors (Excessive temp usage)

Errorstack can be set at System level and also at Session level.

e.g To set errorstack for ORA – 1652 ,Syntax will be

alter system set events ’1652 trace name errorstack level 3′;

If suppose, you want to set it at session level, then

alter session set events ’1652 trace name errorstack level 3′;

You can also use oradebug to take errorstack for particular process (say ospid 1234) as below

sqlplus “/ as sysdba”

oradebug setospid 1234

oradebug unlimit

oradebug dump errorstack 3

There are different levels for the errorstack with level 3 being the highest.

Note: There is no such level as level 10 or 12 which you might
sometimes find on web.

e.g event=’1401 trace name errorstack, level 12′

Extracted from http://www.orafaq.com/faqdbain.htm .
Specifying this will default to level 3.

Let’s see one trace file for error ORA – 942 i.e “table or view does not exist”

SYS>alter session set events ’942 trace name errorstack level 3′;
Session altered.
SYS>select * from err;

select * from err

*ERROR at line 1:ORA-00942: table or view does not exist

Trace file is generated in user_dump_dest as a user process has failed.

Trace file Header
/u01/app/oracle/admin/sql10g/udump/sql10g_ora_23773.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0
System name: SunOS
Node name: test
Release: 5.9
Version: Generic_117171-12
Machine: sun4u
Instance name: sql10g
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 23773, image: oracle@test(TNS V1-V3)

Here we find information about Database version and Process id along with Operating System.

Failing Sql
ksedmp: internal or fatal error
ORA-00942: table or view does not exist
Current SQL statement for this session:
select * from err

Here we can see that faling sql statement is “select * from err”

Call Stack

—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
ksedmp()+744 CALL ksedst() 000000840 ?
FFFFFFFF7FFF06FC ?
000000000 ?
FFFFFFFF7FFED1F0 ?
FFFFFFFF7FFEBF58 ?
FFFFFFFF7FFEC958 ?
ksddoa()+1148 PTR_CALL 0000000000000000 000106000 ? 106323304 ?
106323000 ? 000106323 ?
000106000 ? 106323304 ?
ksdpcg()+276 CALL ksddoa() 00000000F ? 10631DCD0 ?
105502DA8 ? 10631D990 ?
000000180 ? 10631E564 ?
ksdpec()+200 CALL ksdpcg() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
0000003AE ? 00010558F ?
ksfpec()+160 CALL ksdpec() 0000003AE ? 10631EDB0 ?

Immediately after Failing sql, we will find call stack which are actually Oracle functions, which can be used by Oracle Support/ Developement to find the reason for error. You will find sometimes some metalink notes containing reference to functions so as to match Bugs.

PROCESS STATE DUMP
PROCESS STATE
————-
Process global information:
process: 38a1df2f8, call: 38a2b3218, xact: 0, curses: 38a264b38, usrses: 38a264b38
—————————————-
SO: 38a1df2f8, type: 2, owner: 0, flag: INIT/-/-/0×00
(process) Oracle pid=28, calls cur/top: 38a2b3218/3877b9820, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 0
last post received-location: No post
last process to post me: none
last post sent: 0 0 0
last post sent-location: No post
last process posted by me: none
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 38a2039c8
O/S info: user: oracle, term: pts/19, ospid: 23773
OSD pid info: Unix process pid: 23773, image: oracle@test (TNS V1-V3)

SO: 38a264b38, type: 4, owner: 38a1df2f8, flag: INIT/-/-/0×00
(session) sid: 71 trans: 0, creator: 38a1df2f8, flag: (41) USR/- BSY/-/-/-/-/-
SO: 38a264b38, type: 4, owner: 38a1df2f8, flag: INIT/-/-/0×00
(session) sid: 71 trans: 0, creator: 38a1df2f8, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-001C-0000026A, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 3, prv: 0, sql: 385b129b0, psql: 383567fa0, user: 0/SYS
O/S info: user: oracle, term: pts/19, ospid: 23772, machine: test
program: sqlplus@test (TNS V1-V3)
application name: sqlplus@test (TNS V1-V3), hash value=0
last wait for ‘SQL*Net message from client’ blocking sess=0×0 seq=30 wait_time=24569781 seconds since wait started=23
driver id=62657100, #bytes=1, =0

SO: 387f804d0, type: 53, owner: 38a264b38, flag: INIT/-/-/0×00
LIBRARY OBJECT LOCK: lock=387f804d0 handle=385b129b0 mode=N
call pin=0 session pin=0 hpc=0000 hlc=0000
htl=387f80550[387f2d2e8,3877d6288] htb=387f2d2e8 ssga=387f2cce0
user=38a264b38 session=38a264b38 count=1 flags=[0000] savepoint=0×40
LIBRARY OBJECT HANDLE: handle=385b129b0 mutex=385b12ae0(0)
name=select * from err
hash=737b6fa6ce797a460e47cca17ef84a50 timestamp=04-17-2008 13:56:40

Now this gives the process details like Oracle Process id (pid),OS Process Id (ospid) along with wait event which it was waiting on it. It will also show the memory objects (State Objects SO ) associated with this process.

Systemstate dump which is used to diagnose the database hang contains process state dumps for all the processes present in Database.

Execution Plan

As the execution has failed because of ORA- 942 during semantic check, it will not contain the execution plan.