如何使用dbms_scheduler每30分钟运行一次作业 [英] how to use dbms_scheduler to run the job every 30 minutes

问题描述

我正在使用oracle DB，因为我正在监视连接到Java应用程序的oracle DB的性能，所以现在我不得不定期监视DB中活动连接的数量，每30分钟说一遍以下是查询，该查询向我返回活动用户的数量以及名称和数量

I am using oracle DB , now as i am monitoring the performance of oracle DB which is connected to my java application , so rite now i have to monitor the count of active connections in DB at regular intervals lets say after every 30 minutes below is the query which return me the count of active users along with there name and count

select osuser, count(osuser) as active_conn_count 
from v$session 
group by osuser 
order by active_conn_count desc

现在，请告诉我如何在Oracle DB本身的调度程序中进行调度，该调度将每30分钟触发一次.

now please advise how can i make an schedule a job in scheduler in oracle DB itself that will get triggered at every 30 minutes .

推荐答案

我建议将您的统计信息保存在一个表中(例如my_log_table)，在这种情况下，时间表将如下所示:

I would suggest to keep your statistics in a table (say my_log_table), in that case schedule would look something like this:

begin
    DBMS_SCHEDULER.CREATE_JOB (
         job_name             => 'keep_stats',
         job_type             => 'PLSQL_BLOCK',
         job_action           => 'begin insert into my_log_table (mUser,mCnt) (select osuser, count(osuser) as active_conn_count from v$session group by osuser  order by active_conn_count desc);commit;end;',
         start_date           => timestamp '2016-11-07 19:00:00',
         repeat_interval      => 'FREQ=MINUTELY;INTERVAL=30;',
         enabled              => TRUE);
end;

这篇关于如何使用dbms_scheduler每30分钟运行一次作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！