How to find the resource causing ORA-00054?

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.
Who is Participating?
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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.
gs79Connect With a Mentor Commented:
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..


            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 ;
Rao_SAuthor Commented:
thank you, i am going to code the select into a procedure and schedule it to run at the same time as my job.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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.
Rao_SAuthor Commented:
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?
Rao_SAuthor Commented:
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?
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
I use this query to see who is holding locks in our main database (that supports an EBS, formerly called Oracle Applications) system:

select "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 not like '%_GT%' and 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$...".
Rao_SAuthor Commented:
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..
Rao_SAuthor Commented:
thnak you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.