Tag Archives: blocking

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