[Oracle DB] Thủ tục loại bỏ session sau một khoảng thời gian không hoạt động

 -- Với các sesion inactive (không phát sinh câu lệnh sau một khoảng thời gian) việc giới hạn được qui định trên PROFILE của USER tạo sesion đó. Vậy việc đầu tiên là tìm profile tương ứng với user tạo sesion -> cấu hình tham số "idle_time":

SELECT username, profile FROM dba_users WHERE username = 'OAZ21';

-- Giả sử ta tìm được profile là "default" tiến hành cấu hình lại tham số "idle_time":

ALTER PROFILE DEFAULT LIMIT IDLE_TIME 60; -- 60 minutes = 1 hour

-- Hoặc điều chỉnh lại tham số này về trạng thái ban đầu:

ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED;

-- Hoặc khai báo thủ tục và lập job chạy tự động:

WITH sessions
AS (
  SELECT /*+ materialize */
    sess.inst_id, sess.sid, sess.serial#, sess.username, stat.VALUE cpu_used_by_this_session, i.physical_reads, i.block_gets, sess.command, sess.STATUS, sess.lockwait, DECODE(sess.sql_hash_value, 0, sess.prev_hash_value, sess.sql_hash_value) sql_hash_value, RAWTOHEX(DECODE(sess.sql_address, '00', sess.prev_sql_addr, sess.sql_address)) sql_address
  FROM gv$sesstat stat, gv$session sess, gv$sess_io i
  WHERE stat.statistic# = (
      SELECT statistic#
      FROM v$statname
      WHERE name = 'CPU used by this session'
      )
    AND stat.sid = sess.sid
    AND stat.inst_id = sess.inst_id
    AND sess.username IS NOT NULL
    AND i.sid = sess.sid
    AND i.inst_id = sess.inst_id
  ), sqlarea
AS (
  SELECT inst_id, sql_fulltext sql_text, hash_value, RAWTOHEX(Address) AS Address
  FROM gv$sqlarea
  )
SELECT *
FROM sessions, sqlarea
WHERE sessions.inst_id = sqlarea.inst_id
  AND sessions.sql_hash_value = sqlarea.hash_value
  AND sessions.sql_address = sqlarea.address
  AND sessions.STATUS = 'INACTIVE'
ORDER BY cpu_used_by_this_session DESC;


CREATE OR REPLACE PROCEDURE end_idle_sessions AS
  last_call_et_val NUMBER;
BEGIN
  FOR session_rec IN (SELECT sid, serial# FROM v$session WHERE status = 'INACTIVE' AND username IS NOT NULL) LOOP
    SELECT last_call_et INTO last_call_et_val FROM v$session WHERE sid = session_rec.sid AND serial# = session_rec.serial#;
    
    IF last_call_et_val > 21600 THEN -- 60 phút
--       DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM KILL SESSION ''' || session_rec.sid || ',' || session_rec.serial# || '''');
      -- Uncomment below line to actually kill the session
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || session_rec.sid || ',' || session_rec.serial# || '''';
    END IF;
  END LOOP;
END;
/


BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'end_idle_sessions_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN end_idle_sessions; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
    enabled => TRUE
  );
END;
/

===