kill machine function

create or replace
PROCEDURE        gce_kill_machine (p_machine IN VARCHAR2, p_timeout_minutes IN NUMBER DEFAULT 30)
IS
   l_inst_id           NUMBER;
   l_sid               NUMBER;
   l_serial            NUMBER;
   lv_login_minutes    NUMBER;
   l_sql               VARCHAR2 (32000);
   l_session_user      VARCHAR2 (64);
   l_status            VARCHAR2(8);
   l_osuser            VARCHAR2(30);
   l_process           VARCHAR2(24);
   l_machine           VARCHAR2(64);
   l_port              NUMBER;
   l_program           VARCHAR2(48);
   l_module            VARCHAR2(64);
   l_logon_time        date;
   l_event             VARCHAR2(64);
   l_sql_id            VARCHAR2(13);
   l_prev_sql_id       VARCHAR2(13);
   l_sql_text          VARCHAR2(1000);
   l_prev_sql_text     VARCHAR2(1000);
   l_kill_user         VARCHAR2(30);
   l_kill_os_user      VARCHAR2(30);
   l_kill_machine      VARCHAR2(64);
   l_kill_program      VARCHAR2(48);
   cur_sessions        sys_refcursor;
BEGIN

   l_kill_user := sys_context('USERENV','SESSION_USER');
   l_kill_os_user := sys_context('USERENV','OS_USER');
   l_kill_machine := sys_context('USERENV','HOST');
   l_kill_program := sys_context('USERENV','MODULE');

   -- query the information of the session to be killed
   open cur_sessions for
     SELECT inst_id,sid,serial#,username,status,osuser,process,machine,port,program,module,logon_time,
            event,sql_id,prev_sql_id
     FROM gv$session gs
     WHERE username='OPGCEP2'
       AND machine=p_machine
       AND STATUS!='KILLED'
       AND audsid!=USERENV('SESSIONID')
       AND not exists (select distinct inst_id, sid
                       from gv$mystat gm
                       where gm.inst_id=gs.inst_id and gm.sid=gs.sid
                      );
   LOOP
     FETCH cur_sessions
     INTO l_inst_id,l_sid,l_serial,l_session_user,l_status,l_osuser,l_process,l_machine,l_port,l_program,
          l_module,l_logon_time,l_event,l_sql_id,l_prev_sql_id;
     EXIT WHEN cur_sessions%NOTFOUND;

     if l_sql_id is not null then
       begin
         SELECT sql_text into l_sql_text
         FROM gv$sql
         WHERE child_number = 0
         AND inst_id = l_inst_id
         AND sql_id = l_sql_id;
       exception
         when others then
           l_sql_text := '';
       end;
     end if;

     if l_prev_sql_id is not null then
       begin
         SELECT sql_text into l_prev_sql_text
         FROM gv$sql
         WHERE child_number = 0
         AND inst_id = l_inst_id
         AND sql_id = l_prev_sql_id;
       exception
         when others then
           l_prev_sql_text := '';
       end;
     end if;

     -- ONLY ALLOW sessions which logged in p_timeout_minutes(such as 30) minutes ago to be killed
     lv_login_minutes := (sysdate - l_logon_time)*24*60;
     IF lv_login_minutes > p_timeout_minutes
     THEN
        merge into GCE_MAINT.KILL_SESSION_LOG T0
        using(select l_inst_id inst_id,l_sid sid,l_serial serial#,l_kill_user kill_user,l_kill_os_user kill_os_user,l_kill_machine kill_machine,l_kill_program kill_program
                     ,sysdate kill_time,l_session_user session_user,l_status status,l_osuser os_user,l_process process,l_machine machine,l_port port,l_program program,l_module module
                     ,l_logon_time logon_time,l_event event,l_sql_id sql_id,l_prev_sql_id prev_sql_id,l_sql_text sql_text,l_prev_sql_text prev_sql_text from dual) T1
        on (T0.inst_id=T1.inst_id and T0.sid=T1.sid and T0.serial#=T1.serial# and T0.logon_time=T1.logon_time)
        when matched then
          update set t0.kill_user=t1.kill_user, t0.kill_os_user=t1.kill_os_user, t0.kill_machine=t1.kill_machine
          , t0.kill_program=t1.kill_program, t0.kill_time=t1.kill_time, t0.username=t1.session_user, t0.status=t1.status
          , t0.osuser=t1.os_user, t0.process=t1.process, t0.machine=t1.machine, t0.port=t1.port, t0.program=t1.program
          , t0.module=t1.module, t0.event=t1.event, t0.sql_id=t1.sql_id
          , t0.prev_sql_id=t1.prev_sql_id, t0.sql_text=t1.sql_text, t0.prev_sql_text=t1.prev_sql_text
        when not matched then
          insert(
          INST_ID
          ,SID
          ,SERIAL#
          ,KILL_USER
          ,KILL_OS_USER
          ,KILL_MACHINE
          ,KILL_PROGRAM
          ,KILL_TIME
          ,USERNAME
          ,STATUS
          ,OSUSER
          ,PROCESS
          ,MACHINE
          ,PORT
          ,PROGRAM
          ,MODULE
          ,LOGON_TIME
          ,EVENT
          ,SQL_ID
          ,PREV_SQL_ID
          ,SQL_TEXT
          ,PREV_SQL_TEXT
        )
        values(
          l_inst_id
          ,l_sid
          ,l_serial
          ,l_kill_user
          ,l_kill_os_user
          ,l_kill_machine
          ,l_kill_program
          ,sysdate
          ,l_session_user
          ,l_status
          ,l_osuser
          ,l_process
          ,l_machine
          ,l_port
          ,l_program
          ,l_module
          ,l_logon_time
          ,l_event
          ,l_sql_id
          ,l_prev_sql_id
          ,l_sql_text
          ,l_prev_sql_text
        );
        COMMIT;

        -- FORMAT OF KILL SESSION statement
        -- e.g. -- alter system kill session '18,21349,@3' immediate;
        l_sql :=
              'alter system kill session '''
           || TO_CHAR (l_sid)
           || ', '
           || TO_CHAR (l_serial)
           || ', @'
           || TO_CHAR (l_inst_id)
           || ''' immediate';
        DBMS_OUTPUT.PUT_LINE (l_sql);

        -- Actually KILL session now: e.g. alter system kill session '4, 47438, @1' immediate
        EXECUTE IMMEDIATE (l_sql);
     ELSE
        DBMS_OUTPUT.
        PUT_LINE ('*** ERROR: Cannot KILL session ('||to_char(l_sid)||','||to_char(l_serial)||',@'||to_char(l_inst_id)||') since it just logged in '|| to_char(lv_login_minutes) ||' minutes! ***');
     END IF;
   END LOOP;
   CLOSE cur_sessions;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR: No such session');
      ROLLBACK;
      RAISE;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR: '||substr(SQLERRM,1,1000));
      ROLLBACK;
      RAISE;
END;