Oracle Database Scheduler整理

edit by :2018-7-4 15:28

10g引入的這個dbms_scheduler包，替代了之前的dbms_job包，該包功能更強大，可以將job需要的各種資源分開再進行組合。

使用dbms_scheduler建立一個定時任務有兩種形式

1）建立1個SCHEDULER來定義計劃，1個PROGRAM來定義任務內容，再建立1個JOB，為這個JOB指定上面的SCHEDULER和PROGRAM。

2）直接建立JOB，在引數裡面直接指定計劃和任務內容。

1. 參考

http://www.cnblogs.com/lanzi/archive/2012/11/23/2784815.html

https://blog.csdn.net/cnham/article/details/5985172

https://blog.csdn.net/fw0124/article/details/6753715

2. 案例參考

2.1. 授予使用者許可權

要執行DBMS_SCHEDULER需要有CREATE JOB許可權。

要建立外部作業系統命令的job, 還必須有CREATE EXTERNAL JOB許可權。

要對於某個物件進行操作，必須是它的owner，或者對它具有alter許可權，或者有create any job許可權。

要建立/操作job_class，還需要有MANAGE SCHEDULER許可權。job_class都是建立在sys schema中的。

create external job

create job

GRANT SCHEDULER_ADMIN TO scott;

GRANT CREATE JOB TO scott;

GRANT MANAGE SCHEDULER TO scott;

GRANT CREATE RULE, CREATE RULE SET, CREATE EVALUATION CONTEXT TO scott;

GRANT CREATE ANY RULE, CREATE ANY RULE SET,CREATE ANY EVALUATION CONTEXT TO scott;

2.2. 注意時間格式

在使用create_job或者create_schedule前，請先檢查NLS_DATE_LANGUAGE, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT等引數的值, 透過alter session 命令來修改。也可在使用中直接指定格式。例如:

SQL>

select * from nls_session_parameters;

alter session set NLS_DATE_LANGUAGE='AMERICAN';

alter session set NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';

alter session set NLS_TIMESTAMP_TZ_FORMAT='dd-mm-yyyy HH:MI:SS.FF AM TZR'

2.3. 使用定義好的儲存過程

begin

dbms_scheduler.create_job(

job_name=> 'leo.UPDATE_STATS_JOB',

program_name=> 'leo.UPDATE_STATS_2',

start_date=>'2005-06-20 11:00.00.000000 PM +8:00',

repeat_interval=>'FREQ=MONTHLY;INTERVAL=1',

end_date=>'2006-06-20 11:00.00.000000 PM +8:00',

comments=>'Monthly statistics collection job');

end;

/

2.4. 指定外部命令執行

begin

dbms_scheduler.create_job(

job_name=> 'zip_emlog',

job_type=> 'EXECUTABLE',

job_action =>'/home/leo/zip_log.sh',

enabled=>true,

start_date=>'03-07-2005 9:30:00 PM + 8:00',

repeat_interval=>'FREQ=MINUTELY;INTERVAL=30',

end_date=>'31-07-2005 9:30:00 PM + 8:00',

comments=>'Get a latest em log copy and compress it every 30 minutes');

end;

/

2.5. 使用pl/sql塊

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(

program_name => 'LEO.UPDATE_STATS',

program_type => 'PLSQL_BLOCK',

program_action => 'DECLARE sUsername varchar2(30);

cursor cur is select username from dba_users

where username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')

and account_status='OPEN' and substr(username,1,5)<>'MGMT_' ;

BEGIN

OPEN cur;

FETCH cur into sUsername;

WHILE cur%Found

LOOP

DBMS_STATS.GATHER_SCHEMA_STATS (sUsername);

FETCH cur into sUsername;

END LOOP;

close cur;

END;');

END;

/

上面這個例子建立一個名為"UPDATE_STATS"的程式，它的型別是PL/SQL 塊，完成更新非系統使用者的統計資訊的工作。在這個基礎上你可以定製一個合理的計劃，來定期執行這個程式.

2.6. 使用program、scheduler、windows、job組合

---CREATE PROGRAM

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(program_name => 'P_UPDATE_STATS',

program_type => 'PLSQL_BLOCK',

program_action => 'DECLARE sUsername varchar2(30);

cursor cur is select username from dba_users

where username not in (''SYS'',''SYSTEM'',''SYSMAN'',''DBSNMP'')

and account_status=''OPEN'';

BEGIN

OPEN cur;

FETCH cur into sUsername;

WHILE cur%Found

LOOP

DBMS_STATS.GATHER_SCHEMA_STATS (sUsername);

FETCH cur into sUsername;

END LOOP;

close cur;

END;');

END;

/

BEGIN DBMS_SCHEDULER.ENABLE(name => 'P_UPDATE_STATS'); end;

SELECT * FROM DBA_SCHEDULER_PROGRAMS P where p.program_name='P_UPDATE_STATS';

---CREATE SCHEDULER

BEGIN

dbms_scheduler.create_schedule(schedule_name => 'SCH_EVERY_MORING',

start_date=>systimestamp,

--repeat_interval => 'FREQ=DAILY; INTERVAL=1;';

repeat_interval => 'FREQ=HOURLY; INTERVAL=2');

--end_date=>'31-07-2005 9:30:00 PM + 8:00'

END;

select * from dba_scheduler_schedules s where s.schedule_name='SCH_EVERY_MORING';

---CREATE WINDOWS

BEGIN

dbms_scheduler.create_window(window_name => 'EARLY_MORNING_WINDOW',

schedule_name =>'SCH_EVERY_MORING',

duration => numtodsinterval(1, 'hour'),

resource_plan => 'DEFAULT_MAINTENANCE_PLAN');

end;

select * from dba_scheduler_windows w where w.window_name='EARLY_MORNING_WINDOW';

select * from dba_scheduler_window_details w where w.window_name='EARLY_MORNING_WINDOW';

---CREATE JOB

BEGIN

dbms_scheduler.create_job(job_name => 'JOB_UPSTATS',

program_name => 'P_UPDATE_STATS',

schedule_name => 'SCH_EVERY_MORING');

END;

select *from dba_scheduler_jobs j where j.job_name='JOB_UPSTATS';

select * from dba_scheduler_job_log j where j.job_name='JOB_UPSTATS';

2.7. 使用JOB直接建立

begin

dbms_scheduler.create_job(job_name => 'UP_STAT2',

job_type => 'PLSQL_BLOCK',

JOB_ACTION => 'DECLARE sUsername varchar2(30);

cursor cur is select username from dba_users

where username not in (''SYS'',''SYSTEM'',''SYSMAN'',''DBSNMP'') and account_status=''OPEN'';

BEGIN

OPEN cur;

FETCH cur into sUsername;

WHILE cur%Found

LOOP

DBMS_STATS.GATHER_SCHEMA_STATS (sUsername);

FETCH cur into sUsername;

END LOOP;

close cur;

END;',

start_date=>to_timestamp_tz('2018-07-04 00:00:00.000 +08:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),

-- start_date => '04-07-2018 02:30:00 PM +08:00',

repeat_interval => 'FREQ=DAILY; INTERVAL=1;',

enabled => true);

END;

/

select * from dba_schedu

begin

dbms_scheduler.enable(name => 'UP_STAT2');

END;

/

begin

DBMS_SCHEDULER.DISABLE(name => 'UP_STAT2', force => true);

end;

/

select job_name,state from dba_scheduler_jobs where job_name='UP_STAT2';

SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') TIMESTAMP,

job_name,

status,

SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO

FROM user_scheduler_job_run_details s

where s.job_name='UP_STAT2'

ORDER BY log_date;

SELECT job_name, job_class, operation, status FROM USER_SCHEDULER_JOB_LOG where job_name='UP_STAT2';

2.8. 使用引數定義計劃

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM (

program_name => 'PRM_CREATEDUNDATA',

program_action => 'PKG_SCHEDULER.CREATEDUNDATA',

program_type => 'STORED_PROCEDURE',

enabled => FALSE,

number_of_arguments => 5,

comments => '生成催繳資料'

);