查进程的存储过程和定时任务

最新推荐文章于 2022-10-11 15:47:10 发布

原创最新推荐文章于 2022-10-11 15:47:10 发布 · 355 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个用于Oracle数据库中锁定会话监控的解决方案。通过创建表T_LOCKED_SESSION来记录锁定会话的相关信息，并实现了一个过程p_check_locked_session定期检查并更新这些信息。此外，还设置了一个定时任务来定期执行该过程。

-- Create table
create table T_LOCKED_SESSION
(
sid VARCHAR2(100),
serialno VARCHAR2(100),
eventno VARCHAR2(100),
event VARCHAR2(100),
module VARCHAR2(100),
username VARCHAR2(100),
osuser VARCHAR2(100),
prevexecstart VARCHAR2(100),
sqlid VARCHAR2(100),
lockedmode VARCHAR2(100),
action VARCHAR2(100),
inserttime VARCHAR2(100)
)
tablespace EGROUP_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 8
minextents 1
maxextents unlimited
pctincrease 0
);

grant select on v_$session to 用户；
grant select on v_$locked_object to 用户；

create or replace procedure p_check_locked_session
is
v_count varchar2(100);
v_sid varchar2(100);
v_serialNo varchar2(100);
v_event_no varchar2(100);
v_event varchar2(100);
v_module varchar2(100);
v_username varchar2(100);
v_osuser varchar2(100);
v_sql_id varchar2(100);
v_locked_mode varchar2(100);
v_action varchar2(100);
v_prev_exec_start v$session.prev_exec_start%type;
CURSOR cursor_session_info is
select sid,serial#,event#,event,module,username,OSUSER,prev_exec_start,sql_id,locked_mode,action from v$locked_object t1,v$session t2
where username is not null and t1.session_id=t2.sid
order by prev_exec_start, sid;

begin
open cursor_session_info;
loop fetch cursor_session_info into v_sid,v_serialNo,v_event_no,v_event,
v_module,v_username,v_osuser,v_prev_exec_start,v_sql_id,v_locked_mode,v_action;
exit when cursor_session_info%notfound;
select count(sid) into v_count from t_locked_session where sid = v_sid;
if v_count = 0 then
insert into t_locked_session(sid,serialNo,eventNo,event,module,username,osuser,prevExecStart,sqlid,lockedmode,action,inserttime)
values
(v_sid,v_serialNo,v_event_no,v_event,v_module,v_username,v_osuser,to_char(v_prev_exec_start,'yyyy/mm/dd hh24:mi:ss'),v_sql_id,v_locked_mode,v_action,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'));
end if;
end loop;
commit;
close cursor_session_info;
end p_check_locked_session;

/

create or replace procedure p_exec_job
is
job_num NUMBER;
v_count integer;
begin
select count(*) into v_count from user_jobs where what='p_check_locked_session;';
if v_count = 0 then
sys.dbms_job.submit(job =>job_num,
what =>'p_check_locked_session;',
next_date =>to_date('07-12-2010 16:07:42', 'dd-mm-yyyy hh24:mi:ss'),
interval =>'sysdate+30/1440',no_parse=>false);
commit;
end if;

select JOB INTO job_num from user_jobs where what='p_check_locked_session;' and rownum=1;
if job_num > 0 then
dbms_job.run(job_num);
end if;
end;
/

execute p_exec_job();