Purpose
This statement is used to terminate a session. To execute this statement, the user must have the ALTER SYSTEM privilege.
Syntax
ALTER SYSTEM KILL SESSION 'session_id' [IMMEDIATE]
Parameters
| Parameter | Description |
|---|---|
| session_id | The ID of the session to be terminated. You can execute the SHOW PROCESSLIST or SHOW FULL PROCESSLIST statement to view the Id column, or query the GV$SESSION view to obtain it. |
| IMMEDIATE | An optional parameter that specifies whether to immediately terminate the session. If specified, the session will be terminated immediately. Otherwise, the current transaction will be completed before the session is terminated. |
Examples
The following example creates a user named kill_user, grants privileges to the user, logs in to the database as the user, queries the ID of a connected session, and then terminates the session.
obclient [SYS]> CREATE USER kill_user IDENTIFIED BY killuser123;
obclient [SYS]> GRANT CREATE SESSION TO kill_user;
obclient [SYS]> GRANT ALTER SYSTEM TO kill_user;
obclient [KILL_USER]> SHOW PROCESSLIST;
+------------+-----------+----------------------+-----------+---------+------+--------+------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+------------+-----------+----------------------+-----------+---------+------+--------+------------------+
| 3221487726 | KILL_USER | 100.xx.xxx.xxx:34803 | KILL_USER | Query | 0 | ACTIVE | SHOW PROCESSLIST |
| 3221487722 | SYS | 100.xx.xxx.xxx:40025 | SYS | Sleep | 93 | SLEEP | NULL |
+------------+-----------+----------------------+-----------+---------+------+--------+------------------+
1 row in set
obclient [KILL_USER]> ALTER SYSTEM KILL SESSION '3221487726';
Considerations
- Terminating a session may cause uncommitted transactions to roll back.
- Terminating a system session may affect the stability of the database. Proceed with caution.
- You must have the
ALTER SYSTEMprivilege to execute this statement.
References
For more information about querying the number of sessions and their IDs in the current database, see View Tenant Sessions.