• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13248
  • Last Modified:

Cannot truncate table because of a BUSY/NOWAIT error message

Hi Experts,

I am struggling with this message:
ORA-00054 ressource busy and acquire with nowait specified.
when I try to "truncate" a table.

I've read a couple of previous answered questions and they don't seem to help me.

For example this query returns 0 records.

select a.sid,b.serial#,b.machine
from v$access a,v$session b  
where object='my table' and a.sid=b.sid;

I am pretty much alone working with this database since it is a new server not in production. I am also learning the Oracle architecture so I am currently not at ease on how to solve this.

The context is that I was writing a new procedure with PL/SQL Developer,
and using another window to launch it.

Worked a few times but now it breaks at this line:
execute immediate ('truncate table mytable');

Any DDL issued on this table generates the error.

This situation has come up before, and I usually get out of it by issuing a few "commit;" statement, but this time it doesn't work,

any help will be appreciated,

thanks,
0
Aldemenas
Asked:
Aldemenas
  • 4
  • 2
  • 2
1 Solution
 
sventhanCommented:
0
 
sventhanCommented:
By joining v$lock,v$locked_object,v$process you can find it out.....


You can use this query to find out locked objects and who is locking



SELECT oracle_username USERNAME, owner OBJECT_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM v$locked_object v, dba_objects d,
v$lock l, v$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
and v.session_id = s.sid
ORDER BY oracle_username, session_id;
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
joebednarzCommented:
Your query should be looking for table "mytable", not "my table" -- it may show if the table is locked by another process that you might have open...
0
 
AldemenasAuthor Commented:
I ran the query

SELECT oracle_username USERNAME, owner OBJECT_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM v$locked_object v, dba_objects d,
v$lock l, v$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
and v.session_id = s.sid
ORDER BY oracle_username, session_id;

and it outputs

STATUS: Not Blocking
MODE_HELD: Row-X(SX)

for the table I am having trouble truncating,
USER_NAME and OBJECT_OWNER all relate unsurprisingly to me.






0
 
AldemenasAuthor Commented:
and killed the corresponding session.

it works now.
thanks for your help!
0
 
joebednarzCommented:
Well, then you are the problem :)

ROW-X(SX) means you are holding an exclusive-lock on a row... maybe in code previous to the truncate you are inserting or updating, or a previous execution of the code you are testing...

To see which sessions, change the SELECT portion of your query above and add "s.sid" and "s.serial#".  Then you can issue an "ALTER SYSTEM KILL SESSION 'sid, serial#"
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now