find locked session
SELECT SID, serial, seconds_in_wait,
oracle_username, os_user_name, object_name object_locked,locked_mode
FROM (SELECT sess.SID, sess.serial# serial, lo.oracle_username,
lo.os_user_name, ao.object_name, lo.locked_mode,
sess.seconds_in_wait
FROM v$locked_object lo, dba_objects ao, v$session sess
WHERE ao.object_id = lo.object_id
AND lo.session_id = sess.SID
ORDER BY seconds_in_wait DESC)
clean lock session
DECLARE
v_sid NUMBER;
v_serial NUMBER;
v_seconds_in_wait NUMBER;
v_stmt VARCHAR2 (500);
v_lock_time_wait_sec CONSTANT NUMBER (3) := 100;
CURSOR c_data
IS
SELECT SID, serial, seconds_in_wait
/*oracle_username, os_user_name, object_name object_locked,locked_mode, seconds_in_wait*/
FROM (SELECT sess.SID, sess.serial# serial, lo.oracle_username,
lo.os_user_name, ao.object_name, lo.locked_mode,
sess.seconds_in_wait
FROM v$locked_object lo, dba_objects ao, v$session sess
WHERE ao.object_id = lo.object_id
AND lo.session_id = sess.SID
ORDER BY seconds_in_wait DESC)
WHERE ROWNUM <= 5;
BEGIN
FOR xx IN c_data
LOOP
DBMS_LOCK.sleep (1);
BEGIN
v_stmt :=
'alter system kill session '
|| CHR (39)
|| xx.SID
|| ','
|| xx.serial
|| CHR (39);
IF xx.seconds_in_wait > v_lock_time_wait_sec
THEN
EXECUTE IMMEDIATE v_stmt;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
raise_application_error (-20001, SQLERRM);
END;
