Vertica

How to close all Vertica sessions by a user (bit 2)

This post is a part of Bit Series blog and Its going to be a very quick one.

Have you ever faced a scenario as Vertica DBA to close all sessions on a vertica cluster for a specific user only.

The traditional way to do this is

  1. Query sessions table and find all open session for a specific user
  2. Take the session id one by one and then use “select close_session(‘session_id’) command to close each of those sessions

What if there is a smart of way of achieving this tedious task.

Yes, you can do all this with a single command as below.

## First we get all the sessions for the specific users and construct the close_session SQL command for each of the sessions, then we just pass the output of first vsql command as an input to second vsql command.

vsql -U dbadmin -w 'xxxxxx' -At -c "select 'select close_session(''' || session_id || ''');' from sessions where user_name = 'dbadmin';" | vsql  -U dbadmin -w 'xxxxxx'

Next time, you have this task at hand, do it with a single command using VSQL.

Stay tuned for more such upcoming tips and tricks.

2 thoughts on “How to close all Vertica sessions by a user (bit 2)

  1. Great tip!

    But you could also use the CLOSE_USER_SESSIONS function 🙂

    Example:

    dbadmin=> SELECT COUNT(*) FROM sessions WHERE user_name = ‘jim’;
    COUNT
    ——-
    3
    (1 row)

    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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s