Issues ORA 0054 - Resource busy and acquire ---------------------------------------------------------------------------------------------------------------------------------------- Date - Jun 4 14 Env - Prod Ebs 071 When - Creating an index on WF Notification OUt table___________________________________________________________________________________________________________________________________________Contents-------- Observation Error code Description Diagnosis query Available Applied Solution/work around Applied Solution/work around Log Useful links Referred MOS notes DBA views involved Unix/linux commands used Google search terms used___________________________________________________________________________________________________________________________________________Observation------------------------------------------------------------------------------------------------------------------------------------------- ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired occurs while creating index___________________________________________________________________________________________________________________________________________Error code------------------------------------------------------------------------------------------------------------------------------------------- ORA-00054 ___________________________________________________________________________________________________________________________________________Description------------------------------------------------------------------------------------------------------------------------------------------- Error occurs as there are parallel sessions holding locks on the object and your ddl session is 1. unable to acquire lock 2. or timing out before acquiring lock ___________________________________________________________________________________________________________________________________________Diagnosis query------------------------------------------------------------------------------------------------------------------------------------------- 1. to check gv$access2. to check locked objects3. monitor session - check hema/dba/querries4. kill session - check hema/dba/querries 5. get details of session using the objects 1. to check gv$access select s.sid ,s.serial#, s.inst_id, s.status, s.last_call_et/60 as "elapsed", s.logon_time ,s.action, s.module, s.program, s.machine, s.terminal, s.process as "client_pid", s.client_identifier , p.spid as "server_pid" ,s.blocking_session_status ||'.'||s.blocking_session ||'.'||s.blocking_instance as "Blkg: Status.sess.Inst" -- ,s.sql_id, s.prev_sql_id, s.sql_exec_start,s.sql_exec_id -- ,s.state, s.event, s.wait_class, s.SECONDS_IN_WAIT -- ,s.p1Text||'.'||s.p2Text||'.'||s.p3Text as "p1.p2.p3 Text", s.p1||'.'||s.p2||'.'||s.p3 as "p1.p2.p3" from gv$session s, gv$process p, gv$access gva where s.inst_id=p.inst_id and p.addr=s.paddr and s.sid=gva.sid and gva.inst_id=s.inst_id and gva.object='&TAB_NAME' 2. to check locked objects SELECT username U_NAME, owner OBJ_OWNER,object_name, object_type, s.osuser,s.sid,s.serial#,s.inst_id,s.program,s.action,s.module,s.client_identifier, DECODE(l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode) ) MODE_HELD FROM gv$locked_object v, dba_objects d, gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) AND v.session_id = s.sid AND d.object_name in ('WF_NOTIFICATION_OUT') --and v.locked_mode=6 ORDER BY username, session_id; 3. monitor session - check hema/dba/querries 4. kill session - check hema/dba/querries 5. get details of session using the object SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS; 6. get details of application session from port - useful for java applications SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS; 7. Get details of process initiating tcp network connection netstat -ap | grep tcp 0 0 machine.name:34465 oracle.db.com:ncube-lm ESTABLISHED /java ___________________________________________________________________________________________________________________________________________Available Applied Solution/work around------------------------------------------------------------------------------------------------------------------------------------------- Three approaches, 1. Retry the transaction after some time 2. Make the table read only temporarily - suggested by burleson - check impact before trying this ALTER TABLE tab_name READ ONLY SELECT table_name, read_only FROM dba_tables WHERE table_name = '&tab_name'; ALTER TABLE tab_name READ WRITE; 3. check gv$access, gv$locked_objects find who is locking the program. -- refer queries 4. try increasing DDL_LOCK_TIMEOUT for the session and retry the DDL. -- new feature in 11g ? show parameter ddl_lock_timeout alter session set ddl_lock_timeout=300 ;___________________________________________________________________________________________________________________________________________Applied Solution/work around------------------------------------------------------------------------------------------------------------------------------------------- which one worked out here combination of 3 and 4 helped fixing this.___________________________________________________________________________________________________________________________________________Log------------------------------------------------------------------------------------------------------------------------------------------- [applscp@de01bzapp011 ~]$ sqlplus apps SQL> show parameter ddl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 enable_ddl_logging boolean FALSE SQL> conn applsys Enter password: Connected. SQL> show user USER is "APPLSYS" SQL> CREATE INDEX WF_NOTIFICATION_OUT_N1 ON WF_NOTIFICATION_OUT(CORRID) STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) TABLESPACE APPS_TS_QUEUES; 2 3 CREATE INDEX WF_NOTIFICATION_OUT_N1 ON WF_NOTIFICATION_OUT(CORRID) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> alter session set ddl_log ck_timeout=300 ; Session altered. SQL> CREATE INDEX WF_NOTIFICATION_OUT_N1 ON WF_NOTIFICATION_OUT(CORRID) STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) TABLESPACE APPS_TS_QUEUES; 2 3 Index created. SQL> exit ;___________________________________________________________________________________________________________________________________________MOS notes------------------------------------------------------------------------------------------------------------------------------------------- ___________________________________________________________________________________________________________________________________________DBA views involved------------------------------------------------------------------------------------------------------------------------------------------- view column description gv$access object object being used sid, inst_id session inst_id -> gv$session gv$locked_objects object_name object being locked locked_mode 0 - none 1 - null 2 - Row-S SS 3 - Row-X SX 4 - Share 5 S/Row-X SSX 6 Exclusive gv$block block blocked or not [0-no block 1-blocking 2-global] dba_objects object_name object_type gv$session port ___________________________________________________________________________________________________________________________________________Unix/linux commands used------------------------------------------------------------------------------------------------------------------------------------------- netstat -a list all -p process ___________________________________________________________________________________________________________________________________________Useful Links------------------------------------------------------------------------------------------------------------------------------------------- Burleson Description, source, table readonly http://www.dba-oracle.com/t_ora_00054_locks.htm Oracle base stimulation, cause, explanation http://www.oracle-base.com/articles/11g/ddl-lock-timeout-11gr1.phpOracle Doc 11gr1 DDL_Lock_timeout parameter documentation http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams068.htm#REFRN10267Morgan library DDL_LOCK_TIMEOUT parameter demo http://www.morganslibrary.org/reference/ddl_stmnts.htmldecipher infosys Speaks about option 2 http://decipherinfosys.wordpress.com/2007/12/07/oracle-11g-alter-table-read-only/abeytom Find, fix resource busy error http://abeytom.blogspot.in/2012/08/finding-and-fixing-ora-00054-resource.htmlhttps://learnwithme11g.wordpress.com/tag/ddl_lock_timeout/select for update oracle example