ora 00054

[anniez]

I have a file that I can't access. I get (ORA 00054 resource busy and acquire with NOWAIT specified. ) I can't drop the table, rename it, add fields.... I have another copy that is usable but when I migrate it to this instance, I still can't use it. No one else is using it. What can I do now? Thanks for a prompt response!

 

[flstffatboy]

There is a lock on this table. Check v$lock for locks being held and release the lock.

flstffatboy

 

[biot023]

How is this done?
I have a similar problem, but know VERY little about Oracle.

Cheers,
DJL

Common sense is what tells you the world is flat.

 

[martincab]

rem -----------------------------------------------------------------------
rem Filename: lock.sql
rem Purpose: Display database locks and latches (with tables names, etc)
rem Date: 12-Apr-1998
rem Author: Frank Naude (frank@ibi.co.za)
rem -----------------------------------------------------------------------

--set pagesize 23
SET LINESIZE 150
set pause off

col sid format 999999
col serial# format 999999
col username format a12 trunc
col process format a8 trunc
col terminal format a12 trunc
col type format a12 trunc
col lmode format a4 trunc
col lrequest format a4 trunc
col object format a73 trunc

select s.sid, s.serial#,
decode(s.process, null,
decode(substr(p.username,1,1), '?', upper(s.osuser), p.username),
decode( p.username, 'ORACUSR ', upper(s.osuser), s.process)
) process,
nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username,
decode(s.terminal, null, rtrim(p.terminal, chr(0)),
upper(s.terminal)) terminal,
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', decode(u.name, null,
'DICTIONARY OBJECT', u.name||'.'||o.name),
'TD', u.name||'.'||o.name,
'TM', u.name||'.'||o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE',
'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) object
from sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,
sys.v_$process p
where s.paddr = p.addr(+)
and l.sid = s.sid
and l.id1 = o.obj#(+)
and o.owner# = u.user#(+)
and l.type <> 'MR'
UNION ALL /*** LATCH HOLDERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where h.pid = p.pid
and p.addr = s.paddr
UNION ALL /*** LATCH WAITERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from sys.v_$session s, sys.v_$process p, sys.v_$latch l
where latchwait is not null
and p.addr = s.paddr
and p.latchwait = l.addr
/


Martin Cabrera
Oracle DBA/Programmer

 

[Turkbear]

You need to find out what session has the lock and kill that session...If you have the ability ( and rights) to shutdown the instance, warn any users, do a

shutdown abort,startup restricted, shutdown normal, startup

sequence
( sometimes known as a 'hard bounce' ) - this will clear all locks..( If you cannot, have your DBA handle it)

 

[biot023]

I'm pretty sure I have the rights, but as for ability...
Is there a command that will list appropriate tasks?
Like &quot;ps -ef | grep <whatever>&quot;, or something?
&, if so, how do I spot which task is responsible for the lock?
Sorry to keep asking questions.
Cheers for the response,
Doug.

Common sense is what tells you the world is flat.

 

[alexhu]

This script will show you all the locks in the system

You already know which object is locked

Run this and determine the 'SID' that is holding the lock

select b.username,c.sid,c.owner,
c.object,b.lockwait,a.sql_text
from v$sqltext a,
v$session b,
v$access c,
v$locked_object d,
dba_objects e
where a.address=b.sql_address
and a.hash_value=b.sql_hash_value
and b.sid=c.sid
and d.object_id=e.object_id
and e.object_name=c.object
and c.owner!='SYS'
order by b.username ;

Now run this putting the SID in the correct place

select osuser, process from v$session where sid=$SID

You will now have the UNIX user and the process number which you can kill at will

Alex

 

[alexhu]

Also - get the time the user process statred and make sure there are no ther processes owned by that user at the same time. SQL sessions have a habit of staying around in some applications

Alex

 

[gunjan14]

Hi,
after running the above code by martincab, I am getting this result. I do not know how to decode this
SID SERIAL# PROCESS USERNAME TERMINAL TYPE LMOD LREQ OBJECT
------- ------- -------- ------------ ------------ ------------ ---- ---- -------------------------------------------------------------------------
3 1 1218 SYS (orac) ? REDO THREAD X NONE THREAD=1
10 4549 23578 OMC PTS/3 TRANSAC ENQ X NONE RS+SLOT#393289 WRP#2038
10 4549 23578 OMC PTS/3 DML/DATA ENQ RX NONE XMLNSS.RNS_P_CGR_CGRDIR1_WEEK
10 4549 23578 OMC PTS/3 DML/DATA ENQ RX NONE XMLNSS.RNS_P_CGR_CGRDIR1_DAY
10 4549 23578 OMC PTS/3 DML/DATA ENQ RX NONE XMLNSS.RNS_P_CGR_CGRDIR1_HOUR

But if I run my procedure, I am again getting the message. Kindly tell me what to do?

 

[SantaMufasa]

Rather than try to explain the output of Martin's script, I'll just offer my script that is easier to interpret (and use to kill locking session). The script displays these data:
* Owner and Name of locked object,
* Locking Oracle User Name,
* Operating System login Name of locking user,
* Machine Name of locking user
* O/S ID of locking user
* Oracle Sess ID/Serial # (in single quotes for easy copy-and-paste session killing: &quot;ALTER SYSTEM KILL SESSION 'SID,Serial';&quot
* Rollback segment assigned to locking transaction
* Login Date/Time of locking session

Here is the code for my Locks.sql script:

ttitle "Lock Listing"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "Unix|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select	owner||'.'||object_name obj
	,oracle_username||' ('||s.status||')' oruser
	,os_user_name osuser
	,machine computer
	,l.process unix
	,''''||s.sid||','||s.serial#||'''' ss
	,r.name rs
	,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from	v$locked_object l
	,dba_objects o
	,v$session s
	,v$transaction t
	,v$rollname r
where l.object_id = o.object_id
  and s.sid=l.session_id
  and s.taddr=t.addr
  and t.xidusn=r.usn
order by osuser, ss, obj
/
ttitle off
set linesize 132

Let me know if you like it.

Dave
Sandy, Utah, USA @ 20:24 GMT, 13:24 Mountain Time

 

[gunjan14]

Hi Dave,

Thanks a lot.
I do not how I can keep my job without ur expert advice.

Gunjan