Oracle 11.2.0.4 / Windows 2012 Server
Good morning, From time to time, we have locked objects created by user sessions. It identify these locks, I use the below script
SELECT /*+ RULE */
LPAD('--->',DECODE(A.request,0,0,5))||A.SID SID, B.serial#,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
b.status, b.SCHEMANAME SCHEMA,
DECODE(o.owner || '.' || o.object_name, '.', NULL, o.owner || '.' || o.object_name) OBJECT, o.object_type,
b.osuser, b.machine, b.module, b.program,
DECODE(BLOCK, 0, NULL, 'BLOKER' ) || DECODE(request, 0, NULL, '-->WAITER' ) BLOKER,
DECODE (A.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4,
'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', A.lmode) lmode,
DECODE(TRUNC(ctime/86400), 0, TO_CHAR(TO_DATE(ctime, 'SSSSS'), 'HH24:MI:SS'),
TRUNC(ctime/86400) || ' Days + ' || TO_CHAR(TO_DATE(ctime - (TRUNC(ctime/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) TIME,
A.TYPE, 'alter system kill session ' || '''' || a.SID || ', ' || b.serial# ||'''' || ' immediate;' kill_session,
DECODE(object_type, NULL, NULL, 'Dbms_Rowid.rowid_create(1, ' || row_wait_obj# || ', '
|| row_wait_file# ||', ' || row_wait_block#||', ' || row_wait_row# ||')') row_id
FROM v$lock A, v$session b, dba_objects o
WHERE A.SID = b.SID
AND (lmode = 0 OR BLOCK = 1)
AND o.object_id = DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#)
ORDER BY A.id1,A.request;
What I am looking to do is automate (via a windows task scheduled job and SQL Plus) to kill locked sessions/object, rather then manually do it. has anybody / does anybody else have anything in place to cater for this?