oracle 批量清理空闲会话

jiawang

先查看哪些会话可以杀
SELECT
  sid,
  serial#,
  username,
  machine,
  program,
  status,
  TO_CHAR(logon_time, 'yyyy-mm-dd hh24:mi:ss') AS logon_time,
  ROUND(last_call_et / 3600, 2) AS idle_hours,
  sql_id,
  prev_sql_id
FROM v$session
WHERE
  TYPE='USER'
  AND username IS NOT NULL
  AND status='INACTIVE'
  AND last_call_et > 3600  -- 空闲>1小时
ORDER BY last_call_et DESC;
批量生成 kill 命令
SELECT
    'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''';'
FROM v$session
WHERE
    TYPE='USER'
    AND status='INACTIVE'
    AND last_call_et > 180;  -- 空闲 > 3分钟