Solved

How to find the resource causing ORA-00054?

Posted on 2013-05-12
9
1,336 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 125 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 375 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 375 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 375 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

810 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