We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Close All Sessions for a User with a Single Command — Vertica Forum

Close All Sessions for a User with a Single Command

Jim_KnicelyJim_Knicely - Select Field - Administrator
edited May 2019 in Tips from the Team

The Vertica CLOSE_SESSION function can be used to close a user session one session at a time. If you have a user that has opened a bunch of session closing them one at a time would be a very long and tedious process.

That’s where the CLOSE_USER_SESSIONS function comes in handy. It will stop all the sessions for a user, rolling back any transaction currently running, and closes the connection.

Example:

dbadmin=> SELECT COUNT(*) FROM sessions WHERE user_name = 'jim';
COUNT
-------
    33
(1 row)

dbadmin=> SELECT session_id FROM sessions WHERE user_name = 'jim' LIMIT 2;
            session_id
-----------------------------------
v_test_db_node0001-22211:0x20f7e7
v_test_db_node0001-22211:0x20f7ef
(2 rows)

I could close the sessions one by one:

dbadmin=> SELECT CLOSE_SESSION('v_test_db_node0001-22211:0x20f7e7');
                           CLOSE_SESSION
--------------------------------------------------------------------
Session close command sent. Check v_monitor.sessions for progress.
(1 row)

dbadmin=> SELECT CLOSE_SESSION('v_test_db_node0001-22211:0x20f7ef');
                           CLOSE_SESSION
--------------------------------------------------------------------
Session close command sent. Check v_monitor.sessions for progress.
(1 row)

dbadmin=> SELECT COUNT(*) FROM sessions WHERE user_name = 'jim';
COUNT
-------
    31
(1 row

But that would take forever. Instead, I’ll close all of the sessions at once!

dbadmin=> SELECT close_user_sessions('jim');
                             close_user_sessions
------------------------------------------------------------------------------
Close all sessions for user jim sent. Check v_monitor.sessions for progress.
(1 row)

dbadmin=> SELECT COUNT(*) FROM sessions WHERE user_name = 'jim';
COUNT
-------
     0
(1 row)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_USER_SESSIONS.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_SESSION.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/SESSIONS.htm

Have fun!

Sign In or Register to comment.