How To Kill Oracle DB Sessions Who are inActive Only?

Question

I need to Terminate(kill ) All Sessions inside Oracle Db v$session where the status is not Active
To list active and Inactive Sessions i used this statement:

SELECT sid, serial#, status FROM v$session;

I need a Statement to terminate Sessions where status != Active

Answer 1

You may use the following block

declare
  v_command varchar2(500);
begin
  for c in (
            select sid, serial#, machine, module, username, client_info, status, inst_id
              from gv$session v
             where v.status != 'ACTIVE'
             --and v.inst_id = '&i_inst_id'
              order by username
            ) 
  loop
   begin          
      v_command := 'alter system kill session ''' || c.sid || ',' ||c.serial# ||  
                   ',@'||to_char(c.inst_id)||''' immediate';   
     execute immediate v_command;
    exception when others then dbms_output.put_line(sqlerrm); 
   end; 
  end loop;
end;

where v$session is replaced with gv$session dynamic performance view, and INST_ID column collation with the instance number parameter for the case of having RAC DB cases. i.e. even if you have a Single instance database, the above code might be used, too.

Answer 2

There is Only one way to Terminate a session and it is by 'sid,serial#'
this is a Statement for example :

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

So we need to go through a cycle to Perform this Statement for All 'sid,serial#' but Only when status!=active: This Code Sould Solve this Problem :

BEGIN
  FOR sess IN (select sid,serial# from v$session where status<>'ACTIVE')
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || sess.sid  || ',' 
        || sess.serial# || ''' immediate';
  END LOOP;
END;