Kill session in AWS Oracle Database ...
Oracle AWS RDS kill session
connections on an RDS Oracle DB
Kill session in AWS Oracle Database
[oracle@rac01 ~]$ sqlplus 'vihaan@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=************.compute.amazonaws.com)(Port=1521))(CONNECT_DATA=(SID=orcl)))' SQL*Plus: Release 12.2.0.1.0 Production on Sat May 5 14:58:57 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: SQL> ******************************************* This Will NOT work !!!!!!!! set linesize 200 pagesize 300 col kill for a70 select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill,username,sql_id from gv$session where 1=1 -- and username='username' -- and status = 'INACTIVE' and type != 'BACKGROUND';
********************************************* select LOGINS from V$INSTANCE; we need to use this pakage rdsadmin.rdsadmin_util to kill session in oracle aws rds exec rdsadmin.rdsadmin_util.kill(627,35083); select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE'; SID SERIAL# STATUS USERNAME ---------- ---------- -------- ------------------------------ 40 61773 INACTIVE RDSADMIN SQL> select SID, SERIAL#, STATUS,username from V$SESSION where STATUS='INACTIVE'; SID SERIAL# STATUS USERNAME ---------- ---------- -------- ------------------------------ 40 61773 INACTIVE RDSADMIN SQL> exec rdsadmin.rdsadmin_util.kill(40,61773); PL/SQL procedure successfully completed. SQL> set linesize 200 pagesize 300 sql> select sid, serial#, status,username from v$session where status='INACTIVE'; SID SERIAL# STATUS USERNAME ---------- ---------- -------- ------------------------------ 40 61773 INACTIVE RDSADMIN SQL> exec rdsadmin.rdsadmin_util.kill(40,61773); PL/SQL procedure successfully completed. begin rdsadmin.rdsadmin_util.kill( sid => &sid, serial => &serial_number, method => 'IMMEDIATE'); end; / SQL> exec rdsadmin.rdsadmin_util.kill(40,61777,'IMMEDIATE'); PL/SQL procedure successfully completed. to Kill session ! begin rdsadmin.rdsadmin_util.kill( sid => &sid, serial => &serial_number, method => 'IMMEDIATE'); end; / Via Script set linesize 200 pagesize 300 col kill for a70 select 'exec rdsadmin.rdsadmin_util.kill('|| sid ||',' || serial# || ');' kill from v$session where username in ('&Username'); -- session with Immediate !!! set linesize 200 pagesize 300 select 'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill , ' -- ',username ,status,blocking_session,state,final_blocking_session,sql_id from v$session s where 1=1 -- and username = upper('') and STATUS != 'ACTIVE' and s.Type != 'BACKGROUND'; KILL '--' USERNAME STATUS BLOCKING_SESSION STATE FINAL_BLOCKING_SESSION SQL_ID ---------------------------------------------------------------------- ---- -------------------- -------- ---------------- ------------------- ---------------------- ------------- exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- RDSADMIN INACTIVE WAITING SQL> SQL> exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); PL/SQL procedure successfully completed. set linesize 300 col username for a20 select 'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill , ' -- ', s.inst_id, -- s.sid, -- s.serial#, p.spid, s.username, s.program, s.sql_id from gv$session s, gv$process p where 1=1 and p.addr = s.paddr and p.inst_id = s.inst_id and s.type != 'BACKGROUND' ; KILL '--' INST_ID SPID USERNAME PROGRAM SQL_ID ---------------------------------------------------------------------- ---- ---------- ------------------------ -------------------- ------------------------------------------------ ------------- exec rdsadmin.rdsadmin_util.kill(40,61783, 'IMMEDIATE'); -- 1 27035 RDSADMIN JDBC Thin Client exec rdsadmin.rdsadmin_util.kill(21,62867, 'IMMEDIATE'); -- 1 23889 VIHAAN sqlplus@rac01.*****.net (TNS V1-V3) 6v5f6v3upz5mu
1 comment:
set linesize 300 pagesize 300
col kill for a70
col EVENT for a10 wrap
select 'exec rdsadmin.rdsadmin_util.kill('||s.sid||','||s.serial#||', ''IMMEDIATE'''||');' kill , ' -- ',username ,status,blocking_session,state,final_blocking_session,sql_id,PREV_SQL_ID
-- ,EVENT
from v$session s
where 1=1
-- and username not in ('RDSADMIN','MASTER')
-- and username ='SYS'
-- and STATUS = 'ACTIVE'
and s.Type != 'BACKGROUND';
Post a Comment