LIST & KILL Sessions on HANA

If you need to LIST all sessions or KILL a specific active session on HANA then you can use the following SQL queries to do these kind of actions easily..

We can LIST all IDLE sessions with following SQL query ;

SELECT CONNECTION_ID, IDLE_TIME FROM M_CONNECTIONS WHERE CONNECTION_STATUS = ‘IDLE’ AND CONNECTION_TYPE = ‘Remote’ ORDER BY IDLE_TIME DESC;

LIST & KILL Sessions on HANA

To LIST some selected session informations ;

select host, port, connection_id as “connecti_id”, connection_status as “status”, connection_type as “c-type”,transaction_id as “transact_id”, idle_time, auto_commit as “autoc”,client_host,user_name,current_schema_name as “schema”,fetched_record_count as “rec-count” from m_connections order by connection_status;

LIST & KILL Sessions on HANA

Show all RUNNING sessions;

select host,port, connection_id as “c-id”, connection_status as “status”, connection_type as “c-type”,transaction_id as “transac”, idle_time, auto_commit as “autoc”,client_host, user_name,
current_schema_name as “schema”,fetched_record_count as “rec-count” from m_connections where connection_status = ‘RUNNING’ order by connection_status;

LIST & KILL Sessions on HANA

Generate “CANCEL SESSION” syntax from HANA system;

select host, client_host as client,port,user_name, current_schema_name as “schema”, connection_status as “connect stat”, connection_id,
‘ALTER SYSTEM CANCEL SESSION ”’||connection_id||”” as “cmd” from m_connections where connection_status != ” ;

LIST & KILL Sessions on HANA

ALTER SYSTEM CANCEL SESSION  ‘{connection_id}’

Generate “DISCONNECT SESSION” syntax from HANA system;

select host, client_host as client,port,user_name, current_schema_name as “schema”, connection_status as “connect stat”, connection_id,
‘ALTER SYSTEM DISCONNECT SESSION ”’||connection_id||”” as “cmd” from m_connections where connection_status != ” ;

LIST & KILL Sessions on HANA

ALTER SYSTEM DISCONNECT SESSION   ‘{connection_id}’

M_CONNECTIONS System View ;

LIST & KILL Sessions on HANA

 

Leave a Reply


nine − = 4

Blogroll