BLOCK USER KILL

PROCEDURE dpr_user_locks IS
CURSOR c_lock IS
SELECT s.SID, s.serial#, s.username, s.machine, s.osuser
FROM v$lock l, v$session s, dba_objects o
WHERE l.SID = s.SID AND o.object_id(+) = l.id1 AND l.BLOCK = 1;

lock_id INTEGER;
lock_status INTEGER;
stmt VARCHAR2 (512);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_lock LOOP
BEGIN
stmt :=
'ALTER SYSTEM KILL SESSION '
|| ''''
|| i.SID
|| ','
|| i.serial#
|| '''';
lock_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse (lock_id, stmt, DBMS_SQL.native);
lock_status := DBMS_SQL.EXECUTE (lock_id);
DBMS_SQL.close_cursor (lock_id);
DBMS_OUTPUT.put_line ( 'User killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser, INSTR (i.osuser, '\'))
);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.is_open (lock_id) THEN
DBMS_SQL.close_cursor (lock_id);
END IF;

DBMS_OUTPUT.put_line ( 'User can not killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser, INSTR (i.osuser, '\'))
);
END;
END LOOP;
END;