查进程的存储过程和定时任务
-- 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 =&g