Home > Troubleshooting > Resolving Oracle specific errors ( ORA-01031, ORA-00942 etc.)

Resolving Oracle specific errors ( ORA-01031, ORA-00942 etc.)

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=0x0, level=3, mask=0x0)
—– 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=0x0, level=3, mask=0x0)
—– 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=0x257202818 Session=0x257202818 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′;

About these ads
Categories: Troubleshooting
  1. October 27, 2011 at 10:25 am

    Nice one, but is this working with Oracle 9.2.0.8 ?
    Regards
    GregG

  2. October 27, 2011 at 11:05 am

    Hi Greg,

    Its been a while since I used Oracle 9i but I think so.Just try it.
    Enable this events and generate oracle error .

    Thanks,

    Miladin

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 48 other followers

%d bloggers like this: