TRUNCATE TABLE times wrong: Ora-00054:resource busy and acquire with nowait specified

Source: Internet
Author: User
Tags dba

This is because the table with the truncate operation is still executing, and no commit is finished, at which point you can query V$locked_object to get which user's transaction is operating this table.

Experimental Simulation
First, use the Scott user to enter the database and do the following for the existing table T1:
sql> Delete from T1 where name like '%c% ';
23308 rows deleted.
Note that a commit was not executed here.
Then, in another session, the database is performed as a DBA, operating:
sql> truncate TABLE scott.t1;
TRUNCATE TABLE Scott.t1
*
ERROR at line 1:
Ora-00054:resource busy and acquire with nowait specified
The fault reappeared.
For such a problem, the following actions are done using DBA identity:
First determine the object_id of the Action object:
Sql> Select object_id from dba_objects where object_name= ' T1 ';
object_id
----------
52505
Sql> Select Object_id,session_id,oracle_username,os_user_name,process,locked_mode from V$locked_object where object_id=52505;
object_id session_id oracle_username os_user_name PROCESS Locked_mode
--------- ---------- ---------------- ------------- -------- -----------
52505 149 SCOTT Oracle 5333 3
Visible from above, it is the user Scott, who is Sid 149, who is working on this table and can get more information by querying V$session:
Sql> Select sid,serial#,username,process from v$session where sid=149;
SID serial# USERNAME PROCESS
--------- ---------- -------------- ------------
149 136 SCOTT 5333
Then, if the operation allows, you can kill the transaction:
Sql> alter system kill session ' 149,136 ';
You can then perform the truncate operation.

Note:Simply integrate the above statement into the following
Select Sid,serial#,username,process from V$session where sid= (select session_id from v$locked_object where object_id= (SE Lect object_id from dba_objects where object_name= ' &table_name '));

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.