?
Solved

How to find the resource causing ORA-00054?

Posted on 2013-05-12
9
Medium Priority
?
1,682 Views
Last Modified: 2013-05-18
do you have a code that i could use to find which resource is causing a table lock?
i have a packaged procedure which is scheduled to run daily, i get this error ocationally not every day:
-54;ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

i can change the run time of the job to avoid a potential table lock, but i need to find what is causing this error.
0
Comment
Question by:Rao_S
  • 5
  • 3
9 Comments
 

Assisted Solution

by:gs79
gs79 earned 500 total points
ID: 39160067
Try one of these two queries which I use some times: Note I had find these two queries on blog/internet..I do not have that link handy to give the credit..

Thanks..


            select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
            from v$locked_object a ,v$session b,dba_objects c
            WHERE b.sid = a.session_id and a.object_id = c.object_id;

            SELECT c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine,MODULE,MESSAGE,S.TIME_REMAINING
            from v$locked_object a ,v$session b,dba_objects c,V$SESSION_LONGOPS S
            WHERE b.sid = a.session_id and a.object_id = c.object_id and b.sid = S.sid and S.time_remaining > 0 ;
0
 

Author Comment

by:Rao_S
ID: 39160183
thank you, i am going to code the select into a procedure and schedule it to run at the same time as my job.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1500 total points
ID: 39161591
Any query from an interactive screen could cause this error if your scheduled job needs to acquire an exclusive lock on a table.  So, one possibility is a user doing something in a screen that displays data from the table your procedure is trying to get an exclusive lock on.

Obviously I'm guessing here, since I don't know the details of your application, but in a typical multi-user Oracle-based system that has screens for interactive user access, this can easily happen.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Rao_S
ID: 39161927
hi markgeer, yes you are correct, the job which is throwing a -54 is a pl/sql procedure which runs at 6am every morning. the job first truncates the table, and then runs a select and inserts into the table.

we think the error is comming from the truncate. we think we know that the possibility of anybody else trying to insert or update that table is negligible.

so we think that the table is lock by the procdure from the previous day's run.
so we need a query which will show us who is holding the lock on the table.

i tried to open a sqlplus window and executed the procedure, it shows me that procedure is waiting the table, i want to see who is holding the table.....   any suggestion?
0
 

Author Comment

by:Rao_S
ID: 39161933
thank you gs79, i used your first query and it shows me that the procedure is waiting for the table, is there a way to see what is holding the table?
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1500 total points
ID: 39162405
I use this query to see who is holding locks in our main database (that supports an EBS, formerly called Oracle Applications) system:

select o.name "Locked object", l.sid, s.serial#, p.spid, l.inst_id,
round(decode(substr(s.module,1,3),'MWA',(sysdate - s.logon_time) * 1440,l.ctime) /60,0) "Minutes",
decode(l.lmode,'1','-','2','RS','3','RX','4','S','5','SRX','6','X') "Mode", l.type,
substr(s.action,1,30) "Action",
substr(s.module,1,20) "Module",
s.username, substr(s.osuser,1,10)"OS user",
substr(s.machine,1,12) "Machine"
from sys.obj$ o, gv$session s, gv$lock l, gv$locked_object lo, gv$process p
where lo.session_id = l.sid and l.sid > 5
and lo.inst_id = l.inst_id
and (lo.xidsqn = l.id2 or lo.object_id = l.id1)
and lo.session_id = s.sid and lo.object_id = o.obj#
and lo.inst_id = s.inst_id
and p.addr = s.paddr
and p.inst_id = s.inst_id
and o.name not like '%_GT%' and o.name not like '%TEMP'
order by 6,2,1;

If your application does not populate the "Action" and "Module" columns of gv$session reliably, you want to select the username column there instead.

Also, if you don't use RAC, you can use the "v$..." views instead of gv$...".
0
 

Author Comment

by:Rao_S
ID: 39165923
thank you markgeer,
i opened multiple sessions of sqlplus and caused a lock..i was trying to understand what mode and type mean..i have attached a file, please take a look..
Locks1.xls
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1500 total points
ID: 39166104
Here's a link to the Oracle10 documentation on v$lock (where those columns are defined).  If you have Oracle11, these columns and values haven't changed much, if at al, since the oracle10 documentation was produced.

http://docs.oracle.com/cd/B14117_01/server.101/b10755/dynviews_1123.htm
0
 

Author Closing Comment

by:Rao_S
ID: 39177741
thnak you!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question