Solved

How to find the resource causing ORA-00054?

Posted on 2013-05-12
9
1,292 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
 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Subquery bad Join 11 59
history tablespace temp usage 2 31
Consolidating oracle query results to a single line 8 53
Oracle 12c database link between pdb not working 20 45
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

896 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now