Skip to content
December 29, 2011 / doganay

ORA-00054: resource busy and acquire with NOWAIT specified

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

 

Hi,

 

In 11g there is a parameter : ddl_lock_timeout

If you set this parameter (seconds), your ddl wait that seconds.

ALTER SESSION SET ddl_lock_timeout=60;
-- Run your DDL
-- You are waiting 60 seconds now, you can observe your lock

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams068.htm
Then, you can observer which session is blocking yours, etc:

col object format a30
col username format a20
col sidserial format a12
set linesize 200

SELECT a.SID||','||s.serial# SIDserial, s.last_call_et, s.status,s.sql_hash_value, s.username, s.sql_hash_value, a.owner || '.' || a.OBJECT OBJECT, s.lockwait,s.osuser
  FROM gv$session s, gv$access a
 WHERE s.SID = a.SID
   and s.inst_id = a.inst_id
   AND a.owner != 'SYS'
   --and s.status ='ACTIVE'
   AND UPPER (SUBSTR (a.OBJECT, 1, 2)) != 'V$'
   AND a.OBJECT = upper(trim('&object_name'));

try and pray 🙂

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: