Who's accessing a particular table

While trying to alter any table or index, you may see error similar to below

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

You can find out who's accessing the object and may kill that session to proceed with altering the object.

SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT') ;

and the below statement will generate the commands to kill those sesssions.

select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT') ;

and the below statement will generate the command to kill sessions on another instance as well.

select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||',@'||b.inst_id||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT')
/

select
o.object_type,
o.object_name,
DECODE(v.locked_mode,
1, 'no lock',
2, 'row share (SS)',
3, 'row exclusive (SX)',
4, 'shared table (S)',
5, 'shared row exclusive (SSX)',
6, 'exclusive (X)') lock_mode,
v.oracle_username,
v.os_user_name,
v.session_id
from
all_objects o,
gv$locked_object v
where
o.object_id = v.object_id;


SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id

Query the session_longops

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done, to_char(sysdate + TIME_REMAINING/3600/24,'MM-DD-YYYY, HH24:MI:SS') end_at from v$session_longops
where totalwork > sofar AND username like 'SCOTT%'
/

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The ‘ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired’ error can probably be avoided on a running system by setting the session ddl_lock_timeout e.g. ‘alter session set ddl_lock_timeout=5;’ will tell Oracle to retry for 5 seconds.

How to get explain plan and predicate information while running the SQL Statement

SQL> set autotrace traceonly explain
SQL> select sysdate from dual ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

SQL>