Query All the Locked sessions using below SQL and based on your OBJECT find the session locking it
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
Kill the session by below ALTER statement. To perform this operations you need to have full db access, if you face issue, check with your DBA.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
at erp, erp, oracleapps, oracleapps erp, oracle erp, oracle ebusiness, oracle application,
about oracle, oracle developer, oracle jobs, finance software, finance accounting accounting, r12 oracle, what is oracle database, oracle database, Oracle 11g, 11g database
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
Kill the session by below ALTER statement. To perform this operations you need to have full db access, if you face issue, check with your DBA.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
at erp, erp, oracleapps, oracleapps erp, oracle erp, oracle ebusiness, oracle application,
about oracle, oracle developer, oracle jobs, finance software, finance accounting accounting, r12 oracle, what is oracle database, oracle database, Oracle 11g, 11g database
2 comments:
Its Very useful and helped me to resolve the locking session issue. Thank you so much.
Good Blog. Thanks for sharing this information and helpful for me
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Oracle Integration Cloud Online Training
Oracle Fusion Technical Online Training
Post a Comment