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 | Specifies the ID of the session to be terminated. You can view the Id column by executing SHOW PROCESSLIST or SHOW FULL PROCESSLIST, or query the GV$SESSION view. |
| IMMEDIATE | An optional parameter that specifies whether to terminate the session immediately. If specified, the session will be terminated immediately; otherwise, it will wait for the current transaction to complete. |
Examples
The following example creates a user named kill_user, grants privileges to the user, logs in to the database using 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 database stability. Proceed with caution.
- You need the
ALTER SYSTEMprivilege to execute this command.
References
For more information on querying the number of sessions and their IDs in the current database, see View Tenant Sessions.