DBMS_SCHEDULER的使用(二)
osanu_create_job',
SCHEDULE_NAME => 'SCHEDULE_ROSANU');
END;
-- PL/SQL 过程已成功完成。
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 't_xifenfei_job',
PROGRAM_NAME => 'program_rosanu',
COMMENTS => 'rosanu_create_job',
SCHEDULE_NAME => 'SCHEDULE_ROSANU',
AUTO_DROP => FALSE,
ENABLED => TRUE);
END;
-- PL/SQL 过程已成功完成。
SELECT R_ID, TO_CHAR(R_DATE, 'yyyy-mm-dd hh24:mi:ss') FROM T_ROSANU;
/*
R_ID TO_CHAR(R_DATE,'yyyy-mm-dd hh24:mi:ss')
---------- --------------------------------------
job 2013-03-29 21:39:11
job 2013-03-29 21:37:11
job 2013-03-29 21:38:11
program 2013-03-29 21:39:01
program 2013-03-29 21:40:01
*/
从这里可以看出CREATE_SCHEDULE把执行计划部分从CREATE_JOB中独立出来，使得控制力度更大，更加灵活；
补充说明:
1.还可以通过创建JOB_CLASS更加灵活的控制资源的使用情况，必须通过修改JOB_CLASS中的resource_consumer_group实现资源控制，service对应到
数据库的service，可以实现rac中在哪个节点执行等等；
2.使用DBMS_SCHEDULER.set_attribute来修改相关属相如：
[sql]
EXEC DBMS_SCHEDULER.set_attribute('GATHER_STATS_JOB','JOB_CLASS', 'AUTO_TASKS_JOB_CLASS2');
EXEC dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','REPEAT_INTERVAL','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=2;byminute=0;bysecond=0');
[sql]
--DBMS_SCHEDULER 运行信息
SELECT JOB_NAME,
STATE,
ENABLED,
TO_CHAR(LAST_START_DATE, 'yyyy-mm-dd hh24:mi:ss'),
SCHEDULE_NAME
FROM DBA_SCHEDULER_JOBS;
--DBMS_SCHEDULER运行成功与否信息
SELECT LOG_ID,
JOB_NAME,
STATUS,
TO_CHAR(ACTUAL_START_DATE, 'yyyy-mm-dd HH24:MI:ss') START_DATE,
TO_CHAR(LOG_DATE, 'yyyy-mm-dd HH24:MI:ss') LOG_DATE
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'JOB_ROSANU'
ORDER BY 4 DESC;
--查询执行时间情况
SELECT T1.WINDOW_NAME, T1.REPEAT_INTERVAL, T1.DURATION
FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2
WHERE T1.WINDOW_NAME = T2.WINDOW_NAME
AND T2.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP';
--修改执行时间
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW',
'REPEAT_INTERVAL',
'freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0');
DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW', 'DURATION', '+002 00:00:00');
END;
[sql]
--将Job JOB_ROSANU变成可运行状态
BEGIN
DBMS_SCHEDULER.ENABLE('JOB_ROSANU');
END;
--查Job运行时间
SELECT T.JOB_NAME,
T.STATE,
T.ENABLED,
TO_CHAR(T.LAST_START_DATE, 'yyyy-mm-dd hh24:mi:ss') AS 最后运行时间,
TO_CHAR(T.NEXT_RUN_DATE, 'yyyy-mm-dd hh24:mi:ss') AS 下次运行时间,
T.SCHEDULE_NAME
FROM DBA_SCHEDULER_JOBS T
WHERE T.JOB_NAME = 'JOB_ROSANU';