/*restrict any software other than SQL*Plus to connect to Oracle database. */
/* Oracle Job Scheduler DBMS_JOB */
create or replace procedure p_jobscheduler is
begin
/* Submit a job to DBMS_JOB as below */
/* jobno is a bind variable you need to declare before running this block*/
/* SYSDATE + 1/18000 makes the job run after every 5 seconds */
DBMS_JOB.SUBMIT (:jobno,'P_SCHEDULE;', SYSDATE, 'SYSDATE+1/18000');
end;
/
/* Procedure should run in INTERNAL/SYS/SYSTEM in order to function*/
CREATE OR REPLACE PROCEDURE P_SCHEDULE IS
CURSOR C_SESSION IS SELECT SID,SERIAL#,USERNAME,MODULE
FROM V$SESSION
WHERE USERNAME IS NOT NULL
AND USERNAME NOT IN ('SYS','SYSTEM','LDBO');
/* Users SYS,SYSTEM,INTERNAL have been allowed to connect via any software */
/* Retrieve all the relevant columns from v$session*/
BEGIN
FOR C_KS IN C_SESSION LOOP
/* Cursor For loop */
IF NOT C_KS.MODULE = 'SQL*Plus' THEN
dbms_output.put_line(C_KS.MODULE);
/* You should be in Oracle 8i and above to make this statement work. */
EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || C_KS.SID || ',' || C_KS.SERIAL# ||
'''');
END IF;
END LOOP;
EXCEPTION
/* Oops..something went wrong !! Have a look ... turn on your serveroutput */
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED' || SQLERRM);
END;
/
No comments:
Post a Comment