Table Locks

TO CHECK FOR TABLE LOCKS

set pagesize 400
set linesize 600
col USERNAME for a15
col OS_USER_NAME for a15
col TERMINAL for a15
col OBJECT_NAME for a30
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal, b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c where a.sid = c.session_id and b.object_id = c.object_id;

Steps for releasing lock on a table:

Finding Locks


select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc

Finding Blocking sessions :

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0
and l1.id1=l2.id1 and l1.id2=l2.id2

select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

Sessions with highest CPU consumption :
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

Sessions with the highest time for a certain wait  :

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

Sessions with highest DB Time usage :

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0; 
 


Step1:To verify the lock object Here is the import query:
---------------------------------------------------------------

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

Step 2: Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

System altered.

How to release a lock in Oracle

This mostly comes from two other sources Killing Oracle Session and What’s blocking my lock?
This involves the system tables v$lock and v$session, and using the ‘ALTER SYSTEM’ statement. This can all be done via sqlplus as the system user (you don’t need to be sysdba).

First, determine who’s holding the lock:

select
 s1.username || '@' || s1.machine
 || ' ( SID,S#=' || s1.sid || ',' || s1.serial# || ' )  is blocking '
 || s2.username || '@' || s2.machine
 || ' ( SID,S#=' || s2.sid || ',' || s2.serial# || ' )'
  AS blocking_status
from
 v$lock l1,
 v$session s1,
 v$lock l2,
 v$session s2
where
 s1.sid = l1.sid
 and s2.sid = l2.sid
 and l1.BLOCK = 1
 and l2.request > 0
 and l1.id1 = l2.id1
 and l2.id2 = l2.id2;

The result set will look like this:

BLOCKING_STATUS
--------------------------------------------------------------------------------
BEN@INTWAREPOD2145 ( SID,S#=134,11102 )  is blocking BEN@INTWAREPOD2145 ( SID,S#=128,30076 )

Now that we know which session is doing the blocking, (134,11102) in this case, we can kill it.

SQL> alter system kill session '134,11102';

Note, the alter system statement is powerful stuff, so take care with it.