- 05 Sep, 2023
- 0 Comments
- 57 Secs Read
ORA-00054: resource busy and acquire with NOWAIT specified
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | Solution 1: Step 1: Identify the session which is locking the objectselect 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_idand OBJECT_NAME='EMP';Step 2: kill that session usingalter system kill session 'sid,serial#'; Solution 2:SQL> alter table emp read only;Session altered.SQL> alter table emp add (mobno varchar2(20));Table altered.Solution 3 (for 10g):DECLARE MYSQL VARCHAR2(250) := 'alter table emp add (mobno 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: SQL> alter session set ddl_lock_timeout = 700;SQL> alter table emp add (mobile varchar2(15)); |