Miladin Modrakovic's Blog: Oraclue

I lost a count how many times I run into ORA-01031 error .Since I am the one executing the query I can see which SQL statement I am running and then focus on specific objects to find missing privilages.

It gets more difficult when end-user get this error.Thanks to Oracle events this is easy to resolve.

Normally I would just enable errrostack tracing on level 3 for specific error and get a user SQL statement.Once I get it I can quickly resolve permission issue .

To get errorstack for ORA-01031 I would run alter statement bellow:

alter system set events ’1031 trace name errorstack level 3;

and then wait for error to happen .It will be recorder into database alert log file .From there I can find trace file located in dump destination.So here is my file:

*** 2011-10-25 10:13:28.025
*** SESSION ID:(84.2771) 2011-10-25 10:13:28.025
*** CLIENT ID:() 2011-10-25 10:13:28.025
*** SERVICE NAME:(test.world) 2011-10-25 10:13:28.025
*** MODULE NAME:() 2011-10-25 10:13:28.025
*** ACTION NAME:() 2011-10-25 10:13:28.025

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0×0, level=3, mask=0×0)
—– Error Stack Dump —–
ORA-01031: insufficient privileges
—– SQL Statement (None) —–
Current SQL information unavailable – no cursor.

—– Call Stack Trace —–
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
——————– ——– ——————– —————————

However errorstack did not produce SQL statement what means I do not know which object is causing this error.

Solution is  to add more diagnostic events to generate additional information.

Dumping library cache with level 10 I will be able to capture it.So here is my statement:
alter system set events ’1031 trace name errorstack level 3; name library_cache level 10′;

Notice that I am running two different actions using one command.

 again this my trace:
*** 2011-10-25 09:31:30.751
*** SESSION ID:(245.633) 2011-10-25 09:31:30.751
*** CLIENT ID:() 2011-10-25 09:31:30.751
*** SERVICE NAME:(test.world) 2011-10-25 09:31:30.751
*** MODULE NAME:() 2011-10-25 09:31:30.751
*** ACTION NAME:() 2011-10-25 09:31:30.751

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0×0, level=3, mask=0×0)
—– Error Stack Dump —–
ORA-01031: insufficient privileges
—– SQL Statement (None) —–
Current SQL information unavailable – no cursor.

—– Call Stack Trace —–
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)

Part above comes from errorstack ..  However if you search your trace file for word ”library” you will get first occurence for  library dump  and see objects in question ( TEST.TEST_QUEUE ) in my case.
proc=0x25419d9e8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8476, pg=0

LibraryObjectLock:  Address=0x25a903f18 Handle=0x259a47d80 Mode=N CallPin=0x25a903d18 CanBeBrokenCount=2 Incarnation=1 ExecutionCount=0

User=0×257202818 Session=0×257202818 ReferenceCount=1 Flags=PNC/[0002] SavepointNum=d1
LibraryHandle:  Address=0x259a47d80 Hash=150a21c LockMode=N PinMode=S LoadLockMode=0 Status=VALD
ObjectName:  Name=TEST.TEST_QUEUE
FullHashValue=93a71dce5afcd4f5d2a394790150a21c Namespace=QUEUE(10) Type=QUEUE(24) Identifier=105660 OwnerIdn=116
To disable tracing simple run command

alter system set events ’1031 trace name errorstack off; name library_cache off’;

Of course you can use method for other oracle errors  like  ORA-00942 .Just change error number.

e.g.

alter system set events ’942 trace name errorstack level 3; name library_cache level 10′;