ADMINISTRATION
Monday, July 30, 2018
Tuesday, July 3, 2018
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00054: resource busy and acquire with NOWAIT specified Cause: The NOWAIT keyword forced a return to the command prompt because a resource was unavailable for a LOCK TABLE or SELECT FOR UPDATE command. Action: Try the command after a few minutes or enter the command without the NOWAIT keyword. Example: SQL> alter table emp add (mobile varchar2(15)); * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified How to avoid the ORA-00054: - Execute DDL at off-peak hours, when database is idle. - Execute DDL in maintenance window. - Find and Kill the session that is preventing the exclusive lock. Other Solutions: Solution 1: In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait for the object to become available, simply specify how long you would like it to wait: SQL> alter session set ddl_lock_timeout = 600; Session altered. SQL> alter table emp add (mobile varchar2(15)); Table altered. Solution 2: Also In 11g, you can mark your table as read-only to prevent DML: SQL> alter table emp read only; Session altered. SQL> alter table emp add (mobile varchar2(15)); Table altered. Solution 3 (for 10g): DECLARE MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))'; IN_USE_EXCEPTION EXCEPTION; PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54); BEGIN WHILE TRUE LOOP BEGIN EXECUTE IMMEDIATE MYSQL; EXIT; EXCEPTION WHEN IN_USE_EXCEPTION THEN NULL; END; DBMS_LOCK.SLEEP(1); END LOOP; END; Solution 4: Step 1: Identify the session which is locking the object 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='EMP'; Step 2: kill that session using alter system kill session 'sid,serial#';
Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e0.htm
Subscribe to:
Posts (Atom)