一些关于sch的相关查询
select owner,job_name,status,to_char(c.ADDITIONAL_INFO) From dba_scheduler_job_log c where job_name='GATHER_STATS_JOB';
select owner,job_name,SCHEDULE_TYPE,SCHEDULE_NAME From dba_scheduler_jobs where job_name='GATHER_STATS_JOB';
select d.WINDOW_GROUP_NAME,d.WINDOW_NAME from dba_scheduler_wingroup_members d;
select *from dba_scheduler_windows e
DBMS_SCHEDULER.set_attribute(
---查看资源计划类
select * from dba_rsrc_consumer_groups;
select * From dba_rsrc_plans;
SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES
select window_name,resource_plan from dba_scheduler_windows
以前一般使用dbms_job来创建job,oracle10g以后推荐使用dbms_scheduler来创建定时任务,dbms_scheduler功能更为强大。
一个创建job的例子:
begin
sys.dbms_scheduler.create_job(job_name => 'CMDEV.每天执行某件事',
job_type => 'STORED_PROCEDURE',
job_action => 'SP_ExecSomething',
start_date => to_date('01-06-2017 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Daily;Interval=1;ByHour=23;ByMinute=00',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => '');
end;
/
说明:
1、job_name: 任务名称
2、job_type:有三种类型,PL/SQL Block、Stored procedure、Executable
3、job_action:根据job_type的不同,有不同的含义
如果job_type指定的是存储过程,就需要指定存储过程的名字;
如果job_type指定的是PL/SQL块,就需要输入完整的PL/SQL代码;
如果job_type指定的外部程序,就需要输入script的名称或者操作系统的指令名
4、start_date:开始时间
5、repeat_interval:运行的时间间隔,上面例子是每天23点运行一次
6、end_date:到期时间
7、enabled:创建后自动激活
8、auto_drop:默认true,即当job执行完毕都到期是否直接删除job
9、comments:备注
导出job的定义语句:
SELECT dbms_me
select job_name,state from dba_scheduler_jobs;
select owner,job_name, status,error#,cpu_used,additional_info from dba_scheduler_job_run_details where error#>0;
/* 创建可执行程序 */
begin
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'peace_sj_his.PROG_DATASYNC',
program_action => 'peace_sj_his.P_DATASYNC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 3,
comments => '数据同步程序',
enabled => false
);
end;
/
/* 设置可执行程序的输入参数 */
begin
DBMS_SCHEDULER.define_program_argument(
program_name => 'peace_sj_his.PROG_DATASYNC',
argument_position => 1,
argument_type => 'VARCHAR2',
default_value => ''
);
DBMS_SCHEDULER.define_program_argument(
program_name => 'peace_sj_his.PROG_DATASYNC',
argument_position => 2,
argument_type => 'VARCHAR2',
default_value => ''
);
DBMS_SCHEDULER.define_program_argument(
program_name => 'peace_sj_his.PROG_DATASYNC',
argument_position => 3,
argument_type => 'VARCHAR2',
default_value => ''
);
END;
/
/* 创建调度表 */
begin
DBMS_SCHEDULER.create_schedule(
schedule_name => 'peace_sj_his.SCH_DATASYNC',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => sysdate,
comments => '数据同步调度'
);
end;
/
/* 创建作业 */
begin
DBMS_SCHEDULER.create_job(
job_name => 'peace_sj_his.JOB_PPTN',
program_name => 'peace_sj_his.PROG_DATASYNC',
schedule_name => 'peace_sj_his.SCH_DATASYNC',
job_class => 'DEFAULT_JOB_CLASS',
comments => '雨量数据同步作业',
auto_drop => false,
enabled => false
);
end;
/
/* 启动可执行程序 */
exec DBMS_SCHEDULER.enable('PROG_DATASYNC');
/* 启动作业 */
exec DBMS_SCHEDULER.enable('JOB_PPTN');
/* 设置不同的作业参数 */
begin
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'peace_sj_his.JOB_PPTN',
argument_position => 1,
argument_value => 'ST_PPTN_R'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'peace_sj_his.JOB_PPTN',
argument_position => 2,
argument_value => 'TM'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'peace_sj_his.JOB_PPTN',
argument_position => 3,
argument_value => 'STCD'
);
end;
/
----Job管理-----------------------------------------------------------------------
/* 禁用Job */
exec dbms_scheduler.disable('JOBTEST');
/* 执行Job */
exec dbms_scheduler.run_job('JOBTEST');
/* 停止Job */
exec dbms_scheduler.stop_job('JOBTEST');
/* 删除Job */
exec dbms_scheduler.drop_job('JOBTEST');
----参考资料----------------------------------------------------------------------
Oracle 10g Scheduler
全面介绍 http://wallimn.iteye.com/blog/1161983
Oracle任务调度之基于时间的任务调度(原创) http://czmmiao.iteye.com/blog/1003505
复制代码
4、创建定时执行的JOB(每天早上5点定时执行)
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_GATHER_TABLE',
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'P_GATHER_TABLE',
REPEAT_INTERVAL => 'FREQ=Daily;INTERVAL=1;BYHOUR=5',
ENABLED => TRUE);
END;
5 调度任务维护
disable
call DBMS_SCHEDULER.disable('USER.JOB_NAME');
call DBMS_SCHEDULER.enable('USER.JOB_NAME');
call DBMS_SCHEDULER.run('USER.JOB_NAME');
tadata.get_ddl( 'PROCOBJ', '每天执行某件事') FROM dual