ORACLE10G 基本job scheduler使用dbms_scheduler 管理 基本job scheduler

example

begin
dbms_scheduler.create_job
(
job_name => 'DAILY_BACKUP_JOB_01',
job_type => 'STORED_PROCEDURE',
program_name => 'DAILY_BACKUP_PROGRAM',
schedule_name => 'DAILY_BACKUP_WINDOW'
);
end;

dbms_jobs

1.the three main components of the Scheduler -- job , program ,schedules.
2.advanced scheduler components : job classes , windows , resource groups.

(1)JOB 範例
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."P70_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'declare
first_day date;
last_day date;
begin
first_day :=sysdate;
last_day := add_months(first_day,6);
dbms_output.put_line(to_char(last_day,''YYYYMMDD''));
end;',
start_date => systimestamp at time zone '+8:00',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;

(2)JOB + programe 範例
BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'job_type');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'job_action');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'number_of_arguments');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'program_name', value => 'SYS.P70');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'start_date', value => to_timestamp_tz('2006-02-22 22:59:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
END;



(3) programe 範例
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'SYS.P70',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'SYS.P70',
attribute=>'PROGRAM_ACTION',
value=>'declare
first_day2 date;
last_day date;
begin
first_day2 :=sysdate;
last_day := add_months(first_day2,6);
dbms_output.put_line(to_char(last_day,''YYYYMMDD''));
end;');
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'SYS.P70');
END;

(4)SCHEDULER 範例
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=MINUTELY',
start_date => systimestamp at time zone '+8:00',
end_date => to_timestamp_tz('2006-02-22 23:00:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
schedule_name => '"SYS"."P70_SCHEDULE"');
END;

修改排程時間
BEGIN
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_SCHEDULE"', attribute => 'end_date', value => to_timestamp_tz('2006-02-25 23:20:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
END;

(5)SCHEDULER + JOB + programe 範例
BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'start_date');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'repeat_interval');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'end_date');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'schedule_name', value => 'SYS.P70_SCHEDULE');
END;

(6)disable 或 enable job,scheduler,programe
exec dbms_scheduler.enable(SYS.P70_SCHEDULE);
exec dbms_scheduler.disable(SYS.P70_SCHEDULE);

(7)drop job,scheduler,programe
可以用desc dbms_scheduler 查function內容
exec dbms_scheduler.drop_job(SYS.P70_SCHEDULE);
exec dbms_scheduler.drop_window(SYS.P70_SCHEDULE);

使用dbms_scheduler 管理 進階job scheduler
(8)create job class
BEGIN
sys.dbms_scheduler.create_job_class(
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
resource_consumer_group => 'OTHER_GROUPS',
job_class_name => 'MY_JOB_CLASS');
END;

(9)更改job 的 job class
BEGIN
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'job_class', value => 'DEFAULT_JOB_CLASS');
END;

(10)建立window
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'MY_WINDOW',
resource_plan=>'INTERNAL_PLAN',
schedule_name=>'SYS.P70_SCHEDULE',
duration=>numtodsinterval(60, 'minute'),
window_priority=>'LOW',
comments=>'');
END;

(11)查看job 優先權
select job_name,job_priority from dba_scheduler_jobs

(12)create mapping to assign priorities to resource groups
把user scott mapping 到default 群組.
可設定這幾個V$SESSION參數
ORACLE_USER
CLIENT_OS_USER
CLIENT_MACHINE
CLIENT_PROGRAM
SERVICE_NAME
MODULE_NAME
MODULE_NAME_ACTION
SERVICE_MODULE
SERVICE_MODULE_ACTION
EXPLICIT
dbms_resource_manager.oracle_user
dbms_resource_manager.client_os_user

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.oracle_user,
'SCOTT',
'DEFAULT_CONSUMER_GROUP'
);
dbms_resource_manager.submit_pending_area();
END;