监控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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9252210/viewspace-588967/ ，如需转载，请注明出处，否则将追究法律责任。
