Tag Archives: kill session

Manage Lock in 12c Pluggable and RAC Environment Oracle

Check lock in 12c Pluggable database and RAC Oracle Environment

Check the lock in 12c Pluggable Database

SELECT s1.username blkg_user ,s1.machine blkg_machine
,s1.sid blkg_sid ,s1.serial# blkg_serialnum
,s1.sid || ',' || s1.serial# kill_string
,s2.username wait_user,s2.machine wait_machine
,s2.sid wait_sid,s2.serial# wait_serialnum
,lo.object_id blkd_obj_id,do.owner obj_own
,do.object_name obj_name
FROM v$lock l1,v$session s1,v$lock l2
,v$session s2,v$locked_object lo,cdb_objects do
,v$containers u
WHERE s1.sid = l1.sid AND s2.sid = l2.sid
AND l1.id1 = l2.id1 AND s1.sid = lo.session_id
AND lo.object_id = do.object_id AND l1.block = 1
AND l2.request > 0 AND do.con_id = u.con_id;

Check user status in 12c for pluggable database

SELECT c.name ,u.username ,u.account_status
,u.lock_date ,u.created
FROM cdb_users u ,v$containers c
WHERE u.con_id = c.con_id
ORDER BY c.name, u.username;

Check the lock in the RAC Environment

select lckr.sid locksid, seslck.serial# lockserial,lckr.inst_id lockinst,
seslck.username lckuser, wt.sid waitsid, sewt.serial# waitserial,
wt.inst_id waitinst, sewt.username waituser, lckr.type locktype
from gv$lock wt, gv$lock lckr, gv$session seslck , gv$session sewt
where lckr.id1 = wt.id1
and lckr.sid = seslck.sid
and lckr.inst_id=seslck.inst_id
and wt.sid = sewt.sid
and wt.inst_id = sewt.inst_id
and lckr.id2 = wt.id2
and lckr.request=0
and wt.lmode=0;

Check Status of User in RAC Environment

select sid,serial#,inst_id,Status from gv$session where username='SCOTT';

Note:In multi tenant environment,use con_id column for PDB identified.

Kill the session of RAC Environment
Need to specify the instance id in last example @2 for instance 2.

alter system kill session 'SID,SERIAL#,@inst_id';

Kill the session in PDB Environment
Need to go the container and kill the session

Alter system set container=PDB1;
alter system kill session 'SID,SERIAL#';

For single instance locking:
https://smarttechways.com/2013/02/19/check-blocking-and-deadlock-in-oracle/Locking for Single Instance