Thursday, December 29, 2011

Disconnect all sessions at a time

DECLARE
SIDD NUMBER(10);
SERIAL NUMBER(10);
CNT NUMBER(4):=0;
QUERY1 VARCHAR2(500);
BEGIN
FOR I IN (
SELECT SID,SERIAL# SER,AUDSID,USERNAME,OSUSER,TERMINAL,PROGRAM,TYPE,LOGON_TIME,STATUS
FROM V$SESSION WHERE  USERNAME='MIS_USER' ) LOOP
SIDD:=I.SID;
SERIAL:=I.SER;
QUERY1:='ALTER SYSTEM DISCONNECT  SESSION  '''||SIDD||','||SERIAL||''' IMMEDIATE' ;
--DBMS_OUTPUT.PUT_LINE(QUERY1);
EXECUTE IMMEDIATE QUERY1;
CNT:=CNT+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE( CNT||'  SESSIONS HAS BEEN DISCONNECTED ' );
END;
/

No comments: