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