How to kill all the Oracle Session for specific user in Oracle Database 11gR2
Posted by Mir Sayeed Hassan on February 4th, 2019
How to kill all the Oracle Session for specific user in Oracle Database 11gR2
Database Status:
sys@TESTDB> select instance_name,status, version from V$instance; INSTANCE_NAME STATUS VERSION ---------------- ------------ ----------------- testdb OPEN 11.2.0.4.0
Check the user session
sys@TESTDB>select count(*) from v$session where username='MIR'; COUNT(*) ---------- 1 sys@TESTDB> select username,status from v$session where username='MIR'; USERNAME STATUS -------------------------------------- MIR INACTIVE
The below query will kill all the active/inactive session for users
sys@TESTDB> select 'alter system kill session '''||sid||', '||serial#||''';' from v$session where username='MIR'; 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';' -------------------------------------------------------------------------------- alter system kill session '171, 10077';