The CANCEL SESSION statement lets you stop long-running sessions. CANCEL SESSION will attempt to cancel the currently active query and end the session.
Required privileges
To view and cancel a session, the user must be a member of the admin role or must have the VIEWACTIVITY and CANCELQUERY parameters set. Non-admin users cannot cancel admin users' sessions.
Synopsis
Parameters
| Parameter | Description |
|---|---|
session_id |
The ID of the session you want to cancel, which can be found with SHOW SESSIONS.CANCEL SESSION accepts a single session ID. If a subquery is used and returns multiple IDs, the CANCEL SESSION statement will fail. To cancel multiple sessions, use CANCEL SESSIONS. |
select_stmt |
A selection query that returns session_id(s) to cancel. |
Example
Cancel a single session
In this example, we use the SHOW SESSIONS statement to get the ID of a session and then pass the ID into the CANCEL SESSION statement:
> SHOW SESSIONS;
+---------+----------------------------------+-----------+...
| node_id | session_id | user_name |...
+---------+----------------------------------+-----------+...
| 1 | 1530c309b1d8d5f00000000000000001 | root |...
+---------+----------------------------------+-----------+...
| 1 | 1530fe0e46d2692e0000000000000001 | maxroach |...
+---------+----------------------------------+-----------+...
> CANCEL SESSION '1530fe0e46d2692e0000000000000001';
You can also cancel a session using a subquery that returns a single session ID:
> CANCEL SESSIONS (WITH x AS (SHOW SESSIONS) SELECT session_id FROM x
WHERE user_name = 'root');
Cancel multiple sessions
Use the SHOW SESSIONS statement to view all active sessions:
> SHOW SESSIONS;
+---------+----------------------------------+-----------+...
| node_id | session_id | user_name |...
+---------+----------------------------------+-----------+...
| 1 | 1530c309b1d8d5f00000000000000001 | root |...
+---------+----------------------------------+-----------+...
| 1 | 1530fe0e46d2692e0000000000000001 | maxroach |...
+---------+----------------------------------+-----------+...
| 1 | 15310cc79671fc6a0000000000000001 | maxroach |...
+---------+----------------------------------+-----------+...
To cancel multiple sessions, nest a SELECT clause that retrieves session_id(s) inside the CANCEL SESSIONS statement:
> CANCEL SESSIONS (WITH x AS (SHOW SESSIONS) SELECT session_id FROM x
WHERE user_name = 'maxroach');
All sessions created by maxroach will be cancelled.
See also
Was this helpful?
