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:

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