Terminate a session in Oracle

Sometimes it is necessary for Oracle to terminate the current session. For example, if you want to perform a management operation, you need to terminate all non-Administrator sessions. The following describes all aspects of session termination, including:

1. Identify which sessions need to be terminated
2. terminate an Active Session
3. terminate an Inactive Session

When a session is terminated, any active transaction of the session is rolled back and all resources are released immediately, such as locks and memoryareas.
Use altersystem kill session to terminate a SESSION. The following example sid 7, serial #15:
Alter systemkill session '7, 15 ';

1. Identify which sessions need to be terminated
To identify the session to be terminated, you need to know the sid and serial # Of the session. You can query the v $ session to obtain it. For example, the following query identifies all sessions of the user wxlun:

SQL> SELECT SID, SERIAL #, STATUS FROM V $ SESSION WHERE USERNAME = 'wxlun ';

Sid serial # STATUS
----------------------------
19713 ACTIVE
95th 27537 INACTIVE

When a session is calling the database, the session status is ACTIVE. When a session is not calling the database, the session status is INACTIVE.

2. terminate an Active Session
If a user session is processing a transaction (ACTIVE) and terminates the session, the transaction is rolled back and the user immediately receives the following information:
ORA-00028: your session has been killed
If the statement continues to be executed before you reconnect to the database after receiving a ORA-00028 error, oracle returns the following information:
ORA-01012: not loggedon)
When a session is performing network I/O or transaction rollback, it cannot be terminated. This type of session can be terminated only after the operation is completed. In this case, resources are released until the session is terminated. In addition, if an ALTERSYSTEM is sent to terminate a session and the session is not terminated after 60 seconds, a message is received indicating that the session has been marked as ended. The status of the session marked as terminated in v $ seession is KILLED, and the status of some terminated services may be PSEUDO.

3. terminate an Inactive Session
If an InactiveSession is terminated, no ORA-00028 error is received immediately. The ORA-00028 error is not received until the user continues to use the terminated session.
When an inactivesession, the session status changes to KILLED, when the user uses this session again and receives a ORA-00028 error, this terminated session will be deleted from the v $ session.

The following is an example of terminating inactive session.

SQL> SELECT SID, SERIAL #, STATUS FROM V $ SESSION WHERE USERNAME = 'wxlun ';

Sid serial # STATUS
----------------------------
95th 27591 INACTIVE

SQL> alter system kill session '20180101 ';

System altered.

SQL> SELECT SID, SERIAL #, STATUS FROM V $ SESSION WHERE USERNAME = 'wxlun ';

Sid serial # STATUS
----------------------------
95th 27591 KILLED

SQL>

Quickly locate trc files generated by other sessions

Oracle kill session

MySQL Master/Slave troubleshooting-session-level parameter replication error

How to view session blocking of Oracle Database