{"id":350,"date":"2024-07-21T15:52:30","date_gmt":"2024-07-21T15:52:30","guid":{"rendered":"https:\/\/humtechno.com\/intro\/?p=350"},"modified":"2024-07-21T15:52:49","modified_gmt":"2024-07-21T15:52:49","slug":"check-blocking-session-and-kill","status":"publish","type":"post","link":"https:\/\/humtechno.com\/intro\/2024\/07\/21\/check-blocking-session-and-kill\/","title":{"rendered":"Check Blocking Session and Kill"},"content":{"rendered":"\n\n\n\n

Find and Kill session at Database Level<\/strong><\/p>\n\n

select ‘alter system kill session’ || ”” || sid || ‘,’ || serial# || ”” || ‘immediate;’, username, sid,serial#,sql_id , wait_time from v$session where (sid ) in
(
select blocking_session from v$session
);<\/p>\n\n

select ‘alter system kill session’ || ”” || sid || ‘,’ || serial# || ”” || ‘immediate;’ , username, sid,serial#,sql_id , wait_time from v$session where sid in
(select holding_session from dba_blockers);<\/p>\n\n

Checky user Level Locking<\/strong><\/p>\n\n

SELECT se.inst_id,se.last_call_et,
lk.SID, se.username, se.OSUser, se.Machine,DECODE (lk.TYPE,’TX’, ‘Transaction’,’TM’, ‘DML’,’UL’, ‘PL\/SQL User Lock’,lk.TYPE)
lock_type, DECODE (lk.lmode, 0, ‘None’, 1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S\/Row-X (SSX)’,
6, ‘xclusive’,
TO_CHAR (lk.lmode))
mode_held,
DECODE (lk.request,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S\/Row-X (SSX)’,
6, ‘Exclusive’,
TO_CHAR (lk.request))
mode_requested,
TO_CHAR (lk.id1) lock_id1,
TO_CHAR (lk.id2) lock_id2,
ob.owner,
ob.object_type,
ob.object_name,
DECODE (lk.Block, 0, ‘No’, 1, ‘Yes’, 2, ‘Global’) block,
se.lockwait
FROM GV$lock lk, dba_objects ob, GV$session se
WHERE lk.TYPE IN (‘TX’, ‘TM’, ‘UL’)
AND lk.SID = se.SID
AND lk.id1 = ob.object_id(+)
AND lk.inst_id = se.inst_id
and last_call_et > 100;<\/p>\n\n

Kill Session at OS find Used Objects<\/strong><\/p>\n\n

select \/+rule+<\/em>\/ ‘kill -9 ‘ || c.spid from v$access a , v$session b ,v$process c
where a.sid=b.sid
and c.addr=b.paddr
and a.object like ‘%F_BATCH%’;<\/p>\n\n

<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":333,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[3],"tags":[],"class_list":["post-350","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/350","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/comments?post=350"}],"version-history":[{"count":2,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/350\/revisions"}],"predecessor-version":[{"id":352,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/350\/revisions\/352"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media\/333"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media?parent=350"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/categories?post=350"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/tags?post=350"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}