Kill Multiple Seesions in Oracle

SQL> set pagesize 1000
select 'ALTER SYSTEM KILL SESSION '''|| sid ||','|| SERIAL# ||''' immediate;'
from v$session
where username = 'CREDIT';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '8,50279' immediate;
ALTER SYSTEM KILL SESSION '274,993' immediate;
ALTER SYSTEM KILL SESSION '287,19417' immediate;
ALTER SYSTEM KILL SESSION '527,20559' immediate;
ALTER SYSTEM KILL SESSION '536,14701' immediate;
ALTER SYSTEM KILL SESSION '774,16943' immediate;
ALTER SYSTEM KILL SESSION '1030,61303' immediate;
ALTER SYSTEM KILL SESSION '1035,49719' immediate;

8 rows selected.

SQL> set pagesize 1000
select 'ALTER SYSTEM KILL SESSION '''||sid || ','|| SERIAL# || ''' immediate;'
from v$session
where username = 'METABASE_CREDIT';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '290,27645' immediate;
ALTER SYSTEM KILL SESSION '528,42505' immediate;
ALTER SYSTEM KILL SESSION '772,20983' immediate;
ALTER SYSTEM KILL SESSION '780,26785' immediate;
ALTER SYSTEM KILL SESSION '790,19381' immediate;
ALTER SYSTEM KILL SESSION '1034,21611' immediate;
ALTER SYSTEM KILL SESSION '1287,28843' immediate;

7 rows selected.

Generate Script to kill multiple oracle sessions of a particular user

SQL> set linesize 150
column sid format 99,999,999
column machine format a30
column osuser format a10
column username format a10
select sid,serial#, status, machine,username, osuser,
            to_char(LOGON_TIME, 'DD-MON-YYYY hh24:mi:ss') as LOGON_TIME
from v$session
where username='CREDIT'
order by logon_time;

        SID    SERIAL# STATUS   MACHINE                        USERNAME   OSUSER     LOGON_TIME
----------- ---------- -------- ------------------------------ ---------- ---------- -----------------------
          8      50279 INACTIVE DBG\CNSHALT000205              CREDIT     dbuser     21-JUN-2018 10:06:55
        274        993 INACTIVE shaahsrarappu1.cn.db.com       CREDIT     rarsdev    21-JUN-2018 11:41:44
        527      20559 INACTIVE shaahsrarappu1.cn.db.com       CREDIT     rarsdev    21-JUN-2018 11:41:44
      1,030      61303 INACTIVE shaahsrarappu1.cn.db.com       CREDIT     rarsdev    21-JUN-2018 11:41:44
        536      14701 INACTIVE shaahsrarappu1.cn.db.com       CREDIT     rarsdev    21-JUN-2018 11:41:44
        774      16943 INACTIVE shaahsrarappu1.cn.db.com       CREDIT     rarsdev    21-JUN-2018 11:41:44
      1,035      49719 INACTIVE DBG\CNSHALT000205              CREDIT     dbuser     21-JUN-2018 13:29:53
        287      19417 INACTIVE DBG\CNSHALT000205              CREDIT     dbuser     21-JUN-2018 13:38:34

8 rows selected.

SQL> set linesize 150
column sid format 99,999,999
column machine format a30
column osuser format a10
column username format a17
select sid,serial#, status, machine,username, osuser,
            to_char(LOGON_TIME, 'DD-MON-YYYY hh24:mi:ss') as LOGON_TIME
from v$session
where username='METABASE_CREDIT'
order by logon_time;

        SID    SERIAL# STATUS   MACHINE                        USERNAME          OSUSER     LOGON_TIME
----------- ---------- -------- ------------------------------ ----------------- ---------- -----------------------
        290      27645 INACTIVE DBG\CNSHALT000205              METABASE_CREDIT   dbuser     21-JUN-2018 10:27:45
        528      42505 INACTIVE shaahsrarappu1.cn.db.com       METABASE_CREDIT   rarsdev    21-JUN-2018 11:41:37
        780      26785 INACTIVE shaahsrarappu1.cn.db.com       METABASE_CREDIT   rarsdev    21-JUN-2018 11:41:37
      1,287      28843 INACTIVE shaahsrarappu1.cn.db.com       METABASE_CREDIT   rarsdev    21-JUN-2018 11:41:37
        790      19381 INACTIVE shaahsrarappu1.cn.db.com       METABASE_CREDIT   rarsdev    21-JUN-2018 11:41:37
      1,034      21611 INACTIVE shaahsrarappu1.cn.db.com       METABASE_CREDIT   rarsdev    21-JUN-2018 11:41:37
        772      20983 INACTIVE DBG\CNSHALT000205              METABASE_CREDIT   dbuser     21-JUN-2018 13:31:09

7 rows selected.

SQL> set linesize 80
set pagesize 1000
select 'ALTER SYSTEM KILL SESSION '''|| sid ||','|| SERIAL# ||''' immediate;'
from v$session
where username = 'CREDIT';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '8,50279' immediate;
ALTER SYSTEM KILL SESSION '274,993' immediate;
ALTER SYSTEM KILL SESSION '287,19417' immediate;
ALTER SYSTEM KILL SESSION '527,20559' immediate;
ALTER SYSTEM KILL SESSION '536,14701' immediate;
ALTER SYSTEM KILL SESSION '774,16943' immediate;
ALTER SYSTEM KILL SESSION '1030,61303' immediate;
ALTER SYSTEM KILL SESSION '1035,49719' immediate;

8 rows selected.

SQL> set linesize 80
set pagesize 1000
select 'ALTER SYSTEM KILL SESSION '''||sid || ','|| SERIAL# || ''' immediate;'
from v$session
where username = 'METABASE_CREDIT';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '290,27645' immediate;
ALTER SYSTEM KILL SESSION '528,42505' immediate;
ALTER SYSTEM KILL SESSION '772,20983' immediate;
ALTER SYSTEM KILL SESSION '780,26785' immediate;
ALTER SYSTEM KILL SESSION '790,19381' immediate;
ALTER SYSTEM KILL SESSION '1034,21611' immediate;
ALTER SYSTEM KILL SESSION '1287,28843' immediate;

7 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26664718/viewspace-2156444/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26664718/viewspace-2156444/