Many Active sessions and Inactive sessions are responsible for
a. Long Running procedures/packages in a new session.
b. Locking of Tables and hence delaying any DML operations.
c. Slowness in database performance.
Steps to be followed to kill those sessions:
a. Kill those Active and InActive sessions using:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
b. Disconnecting the Active and InActive sessions;
ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' (POST_TRANSACTION | IMMEDIATE);
POST transaction: Disconnects sessions after the transaction completes.
IMMEDIATE: Disconnects sessions immediately and rollbacks the changes of DML statements.
Note:
SID and SERIAL# from V$SESSION table.
If database is shared among more schemas, consider instance id(INST_ID) from GV$SESSION.
Then statement would be :
ALTER SYSTEM KILL SESSION 'SID,SERIAL#,@inst_id';
ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#,@inst_id' (POST_TRANSACTION | IMMEDIATE);
For killing mutiple sessions, use the below query:
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''';' FROM GV$SESSION WHERE STATUS='INACTIVE';
For Disconnecting Multiple sessions, use below query:
SELECT 'ALTER SYSTEM DISCONNECT SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' IMMEDIATE;' FROM GV$SESSION WHERE STATUS='INACTIVE';
SELECT 'ALTER SYSTEM DISCONNECT SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' POST_TRANSACTION ;' FROM GV$SESSION WHERE STATUS='INACTIVE';
