ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy.
Action: Retry if necessary.

I show you here how to get around this error and kill the sessions that are preventing the exclusive lock.

Note that you could also (preferred)

  • Run your changes later when the database is idle.
  • Do all DDL during a maintenance window when all users are not logged in

Run in SQL*Plus or SQL developer

select a.sid, a.serial#  from v$session a, v$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id
and OBJECT_NAME='YouTableName';

Then note both the sid and serial and run

alter system kill  session 'sid,serial#' IMMEDIATE;

If this still do not work, your last bullet is to kill the Unix process which is still hanging on your database server!

here is how to find the unix process to kill

Run in SQL*Plus or SQL developer

select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,
p.program,p.terminal,logon_time,module,s.osuser from V$process p,V$session s where s.paddr = p.addr and s.status = 'ACTIVE' and
s.username not like '%SYS%';

and finally run a

kill –9 spid

if this still don’t not work, your best friend is Google

comments powered by Disqus

You might like also

Oracle ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
The Oracle error ORA-00054 can be solved by executing as DBA the following query select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et, p.program,p.terminal,logon_time,module,s.osuser from V$process p,V$session s where s.paddr = p.addr and s.status = &39;ACTIVE&39; and s.username not like &39;%SYS%&39;; This will return the PID of the hanging process You can then SSH to the server ruining oracle and kill the process kill -9 PID …
1536 Days ago
Oracle Please set ORACLE_UNQNAME to database unique name
if you encounter this error while starting oracle /etc/init.d/oraemctl start Starting Oracle EM DB Console: Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name. OK One possible solution is to edit the script # vi /etc/init.d/oraemctl and add the following code in blue …
2046 Days ago
Install XEN Server without an Optical Drive
Citrix XenServer is a complete, managed server virtualization platform built on the powerful Xen hypervisor. Xen technology is widely acknowledged as the fastest and most secure virtualization software in the industry. In case you don’t have any optical drive attached to the server, this small how to will help you to install Xen Server as domain 0 (dom0) using USB flash drive. Download XEN server iso from http://www.citrix.com/English/ss/downloads/index.asp Format your USB drive using FAT32, Use an older version of UNetbootin …
2240 Days ago
Map Synology network drive under linux
How to mount your Synology NAS (or any other NAS brand) shared folder under Linux using CIFS. CIFS stands for "Common Internet File System," also known under the older name SMB (Server Message Block),  which is a network protocol used by Windows clients for issuing file access requests to Windows servers Open a terminal and as root, create as many directory as needed in /mnt/ # sudo mkdir /mnt/video # sudo mkdir /mnt/music Unfortunately there is no frontend or editor …
2673 Days ago
Advanced Oracle Weblogic start/stop script
The Oracle WebLogic 11g application server product line is the industry's most comprehensive Java platform for developing, deploying, and integrating enterprise applications. It provides the foundation for application grid, which is an architecture that enables enterprises to outperform their competitors while minimizing operational costs. …
2675 Days ago
Basic operations with XEN server: export, import of VM templates and guests
More and more I am using XEN at work, and here is a small how to of some very common operations. Citrix® XenServer® is a complete, managed server virtualization platform built on the powerful Xen® hypervisor. Xen technology is widely acknowledged as the fastest and most secure virtualization software in the industry. XenServer is designed for efficient management of Windows® and Linux® virtual servers and delivers cost-effective server consolidation and business continuity. By the way if you succeed installing XEN …
2675 Days ago
Debian Lenny how to
  Short description of how to accomplish some specific task in Debian Lenny. How to install Sun java 1.6 on Debian Lenny How to install Tomcat 6.x on Debian Lenny How to install MySQL on Debian Lenny             …
3005 Days ago
Some useful Unix/Linux Bash alias
Some useful Bash Linux alias taken from my user profile. If you have a long command that you type frequently consider putting it in as an alias. In computing, alias is a command in various command line interpreters (shells) such as Unix shells, 4DOS/4NT and Windows PowerShell, which enables a replacement of a word with another string. It is mainly used for abbreviating a system command, or for adding default arguments to a regularly used command. [WikiPedia] …
3071 Days ago
Useful Unix/Linux bash functions
For day to day hacker, they may be more elegant ways to do it (PERL, AWK), but they have work well for me. Upper case file name or variable, Search and replace in all files content recursively, Renaming all files recursively, Renaming all directory recursively, Copying a set of files from one directory to the other and preserving hierarchy. …
3071 Days ago
Make a VMWARE copy of a live Linux server
My idea is to be able to run my production server (STRATO- OpenSuse) hosting www.waltercedric.com&160; in a VMWARE instance. So I can test and make change to the operating first there before bringing down my host for several hours ;-) I would like to duplicate the whole LINUX system (lets call it PROD for now), including data and make 2 VMWARE instance.. why two instances you may ask? One will be called DEV, and be very instable by definition, it …
3075 Days ago