#Rds oracle kill session serial

IF v_user IN ('SCOTT') THEN -the list can be extendedД®XECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''' Suppose we want to give kill session access to the user SCOTT to kill its own session CREATE OR REPLACE PROCEDURE sys.kill_session(p_sid NUMBER, p_serial NUMBER) This way developer will be more independent and DBA will also get time to work on other priorities This bottleneck can be achieved by creating a procedure and then giving execute on that procedure to the user.

We can give alter system privilege but it will other grants also which Oracle DBA donвЂ™t want. We dont have any privilege like alter system kill session which can be given to the user. They want the oracle kill own session privilege. 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.Often developers ask how we can kill our own session as they dont want to wait for the DBA to respond and kill.

#Rds oracle kill session serial

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. 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. 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. 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. Set linesize 200 pagesize 300 col kill for a70 select 'alter system kill session immediate ' kill,username,sql_id from gv$session where 1=1 - and username='username' - and status = 'INACTIVE' and type != 'BACKGROUND' *********************************************