監控session數量指令碼

寫了一個簡單的job，用作每2分鐘監控v$session中active和inactive的會話記錄，插入到日誌表中。

--1.建立監控表
create table SESSION_MONITOR
AS
select SYSDATE SYS_TIME,
       b.inst_id,
       sum(case when b.STATUS = 'ACTIVE' THEN 1 ELSE 0 END) active,
       sum(case when b.STATUS = 'INACTIVE' THEN 1 ELSE 0 END) inactive
from gv$session b
where  schemaname='LEIZ' 
group by cube(b.inst_id)

--2.建立procedure
create or replace procedure proc_session_monitor
is
begin
       insert into session_monitor
       select SYSDATE SYS_TIME,
       b.inst_id,
       sum(case when b.STATUS = 'ACTIVE' THEN 1 ELSE 0 END) active,
       sum(case when b.STATUS = 'INACTIVE' THEN 1 ELSE 0 END) inactive
from gv$session b
where  schemaname='LEIZ' 
group by cube(b.inst_id);
commit;
end pr_session_monitor;

--3.建立job
begin
  sys.dbms_job.submit(job => :job,
                      what => 'proc_session_monitor();',
                      next_date => sysdate,
                      interval => 'sysdate+2/1440');
  commit;
end;
/

--4.察看job和監控log
select * from user_jobs
select * from session_monitor b
order by b.sys_time asc