oracle 如何使用 dbms_scheduler 每 30 分钟运行一次作业
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40469587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use dbms_scheduler to run the job every 30 minutes
提问by user1881169
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
我正在使用 oracle DB ，现在因为我正在监视连接到我的 java 应用程序的 oracle DB 的性能，所以现在我必须定期监视 DB 中的活动连接数，可以说每 30 分钟后下面是查询返回活跃用户的数量以及名称和数量
select osuser, count(osuser) as active_conn_count
from v$session
group by osuser
order by active_conn_count desc
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 .
现在请告诉我如何在 oracle DB 本身的调度程序中安排一项工作，该工作将每 30 分钟触发一次。
回答by Plirkee
I would suggest to keep your statistics in a table (say my_log_table), in that case schedule would look something like this:
我建议将您的统计数据保存在表格中（例如my_log_table），在这种情况下，时间表将如下所示：
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;
回答by HCekic
There are pretty much examples about dbms_scheduler. It's easy to submit a recurring job. But actually you do not need to do that! Oracle already stores tons of data about database performance, it will be easier and much more accurate to use these statistics. Take a look at the active_session_historyover here.
有很多关于 dbms_scheduler 的例子。提交重复性工作很容易。但实际上你不需要这样做！Oracle 已经存储了大量有关数据库性能的数据，使用这些统计数据会更容易、更准确。看看这里的active_session_history。
Hope this is what you are looking for.
希望这是你正在寻找的。
回答by PiC
First, you create a schedule.
首先，您创建一个计划。
dbms_scheduler.create_schedule( schedule_name => 'EVERY_1ST_SATURDAY'
, repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;BYDAY=1 SAT;BYHOUR=2;BYMINUTE=0'
, start_date => SYSTIMESTAMP
, comments => '1st Saturday of the month 2am.'
);
Optionally, you create a program:
或者，您创建一个程序：
dbms_scheduler.create_program( program_name => 'GATHER_SCHEMA_STATS'
, program_type => 'STORED_PROCEDURE'
, program_action => 'dbms_stats.GATHER_SCHEMA_STATS'
, number_of_arguments => 1
, enabled => FALSE
, comments => 'Gather schema''s stats (e.g. FRAMEWORK''s)'
);
... grant access to it:
...授予访问权限：
GRANT execute ON sys.GATHER_SCHEMA_STATS TO framework;
... and parameters for it:
...及其参数：
DBMS_SCHEDULER.define_program_argument ( 'GATHER_SCHEMA_STATS', 1 , 'ownname', 'VARCHAR2', 'FRAMEWORK' );
Secondly, you create a job:
其次，你创建一个工作：
DBMS_SCHEDULER.CREATE_JOB ( job_name => 'FRAMEWORK.GATHER_FRAMEWORK_STATS_JOB1'
, program_name => 'SYS.GATHER_SCHEMA_STATS'
, schedule_name => 'SYS.EVERY_1ST_SATURDAY'
, enabled => TRUE
, auto_drop => FALSE
, comments => 'Gather FRAMEWORK''s stats on 1st Saturday of the month.'
);
回答by Acacio Lima Rocha MW
repeat_interval => 'FREQ=HOURLY; BYMINUTE=0,30; BYSECOND=0 ', -
repeat_interval => '频率=每小时；BYMINUTE=0,30; BYSECOND=0 ', -
