/*********************************************************
*PURPOSE: To findout blocking sessions and kill them *
*AUTHOR: Shailender Thallam *
**********************************************************/
--
DECLARE
CURSOR c
IS
SELECT c.owner,
c.object_name,
c.object_type,
b.SID ,
b.serial# ,
b.status ,
b.osuser ,
b.machine
FROM v$locked_object a,
v$session b ,
dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id
--AND b.osuser = 'shailender.thallam'
--and c.object_name in ('JSW_CRM_C_HR_COIL_INFO','T_SPCL_BARCODE_CRS2')
;
--
CURSOR d
IS
SELECT SID,
serial#
FROM v$session s
WHERE s.blocking_session IS NOT NULL
--AND osuser = 'shailender.thallam';
--
c_row c%ROWTYPE;
d_row d%ROWTYPE;
--
l_sql VARCHAR2(100);
BEGIN
OPEN c;
LOOP
FETCH c INTO c_row;
EXIT
WHEN c%NOTFOUND;
dbms_output.put_line('in c '||c_row.SID);
l_sql := 'alter system kill session '''||c_row.SID||','||c_row.serial#||'''';
EXECUTE IMMEDIATE l_sql;
COMMIT;
END LOOP;
CLOSE c;
--
--
--
OPEN d;
LOOP
FETCH d INTO d_row;
EXIT
WHEN d%NOTFOUND;
dbms_output.put_line('in d '||d_row.SID);
l_sql := 'alter system kill session '''||d_row.SID||','||d_row.serial#||'''';
EXECUTE IMMEDIATE l_sql;
COMMIT;
END LOOP;
CLOSE d;
END;
/
-- |
/*********************************************************
*PURPOSE: To findout blocking sessions and kill them *
*AUTHOR: Shailender Thallam *
**********************************************************/
--
DECLARE
CURSOR c
IS
SELECT c.owner,
c.object_name,
c.object_type,
b.SID ,
b.serial# ,
b.status ,
b.osuser ,
b.machine
FROM v$locked_object a,
v$session b ,
dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id
--AND b.osuser = 'shailender.thallam'
--and c.object_name in ('JSW_CRM_C_HR_COIL_INFO','T_SPCL_BARCODE_CRS2')
;
--
CURSOR d
IS
SELECT SID,
serial#
FROM v$session s
WHERE s.blocking_session IS NOT NULL
--AND osuser = 'shailender.thallam';
--
c_row c%ROWTYPE;
d_row d%ROWTYPE;
--
l_sql VARCHAR2(100);
BEGIN
OPEN c;
LOOP
FETCH c INTO c_row;
EXIT
WHEN c%NOTFOUND;
dbms_output.put_line('in c '||c_row.SID);
l_sql := 'alter system kill session '''||c_row.SID||','||c_row.serial#||'''';
EXECUTE IMMEDIATE l_sql;
COMMIT;
END LOOP;
CLOSE c;
--
--
--
OPEN d;
LOOP
FETCH d INTO d_row;
EXIT
WHEN d%NOTFOUND;
dbms_output.put_line('in d '||d_row.SID);
l_sql := 'alter system kill session '''||d_row.SID||','||d_row.serial#||'''';
EXECUTE IMMEDIATE l_sql;
COMMIT;
END LOOP;
CLOSE d;
END;
/
--