Oracle Scheduler

botang

1.   普通的基于时间的作业：

1. BEGIN
   
2. DBMS_SCHEDULER.CREATE_PROGRAM(
   
3. program_name=>'"HR"."PROGRAM1"',
   
4. program_action=>'begin
   
5. update employees set salary=salary+1 where employee_id=100;
   
6. commit;
   
7. end;',
   
8. program_type=>'PLSQL_BLOCK',
   
9. number_of_arguments=>0,
   
10. comments=>'program1',
    
11. enabled=>TRUE);
    
12. END;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
   
4. start_date => to_timestamp_tz('2018-08-09 21:25:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
   
5. end_date => to_timestamp_tz('2018-08-09 21:28:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
   
6. comments => 'schedule1',
   
7. schedule_name => '"HR"."SCHEDULE1"');
   
8. END;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB1"',
   
4. program_name => '"HR"."PROGRAM1"',
   
5. schedule_name => '"HR"."SCHEDULE1"',
   
6. job_class => '"DEFAULT_JOB_CLASS"',
   
7. comments => 'job1',
   
8. auto_drop => FALSE,
   
9. enabled => TRUE);
   
10. END;

复制代码

	Operation Detail
	[table] Select Log ID Log Date Operation Status 389 Aug 9, 2018 9:27:30 PM +08:00 COMPLETED 388 Aug 9, 2018 9:27:30 PM +08:00 RUN SUCCEEDED 387 Aug 9, 2018 9:27:00 PM +08:00 RUN SUCCEEDED 386 Aug 9, 2018 9:26:30 PM +08:00 RUN SUCCEEDED 385 Aug 9, 2018 9:26:00 PM +08:00 RUN SUCCEEDED 384 Aug 9, 2018 9:25:30 PM +08:00 RUN SUCCEEDED 383 Aug 9, 2018 9:25:00 PM +08:00 RUN SUCCEEDED

2.   外部的基于时间的作业：
/home/oracle/job2.sh

1. #!/bin/sh
   
2. 
   
3. date +%Y%m%d-%H%M%S >> /home/oracle/job2.log
   

复制代码

1. grant create external job to hr;

复制代码

1. select  * from dba_scheduler_credentials;

复制代码

默认为空。

1. begin
   
2.    dbms_scheduler.create_credential(credential_name => 'credential1',
   
3.    username => 'oracle',
   
4.    password => 'oracle');
   
5. end;

复制代码

1. select  * from dba_scheduler_credentials;

复制代码

	OWNER	CREDENTIAL_NAME	USERNAME	DATABASE_ROLE	WINDOWS_DOMAIN	COMMENTS
1	SYS	CREDENTIAL1	oracle

1. grant execute on credential1 to hr;

复制代码

1. BEGIN
   
2. DBMS_SCHEDULER.CREATE_PROGRAM(
   
3. program_name=>'"HR"."PROGRAM2"',
   
4. program_action=>'/home/oracle/job2.sh',
   
5. program_type=>'EXECUTABLE',
   
6. number_of_arguments=>0,
   
7. comments=>'PROGRAM2',
   
8. enabled=>TRUE);
   
9. END;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
   
4. start_date => to_timestamp_tz('2018-08-09 21:44:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
   
5. end_date => to_timestamp_tz('2018-08-09 21:46:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
   
6. comments => 'schedule2',
   
7. schedule_name => '"HR"."SCHEDULE2"');
   
8. END;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB2"',
   
4. program_name => '"HR"."PROGRAM2"',
   
5. schedule_name => '"HR"."SCHEDULE2"',
   
6. job_class => '"DEFAULT_JOB_CLASS"',
   
7. comments => 'job2',
   
8. auto_drop => FALSE,
   
9. enabled => FALSE);
   
10. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB2"', attribute => 'credential_name', value => '"SYS"."CREDENTIAL1"' );
    
11. sys.dbms_scheduler.enable( '"HR"."JOB2"' );
    
12. END;

复制代码

	Operation Detail
	[table=98%] [table] Select Log ID Log Date Operation Status 394 Aug 9, 2018 9:45:30 PM +08:00 COMPLETED 393 Aug 9, 2018 9:45:30 PM +08:00 RUN SUCCEEDED 392 Aug 9, 2018 9:45:00 PM +08:00 RUN SUCCEEDED 391 Aug 9, 2018 9:44:30 PM +08:00 RUN SUCCEEDED 390 Aug 9, 2018 9:44:00 PM +08:00 RUN SUCCEEDED

[/td][/tr]
[/table]