?
Solved

How to find the resource causing ORA-00054?

Posted on 2013-05-12
9
Medium Priority
?
1,514 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 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