oracle小技巧

窗口

begin

dbms_scheduler.create_schedule(schedule_name => 's5',repeat_interval=>'freq=daily;

byhour=9;byminute=30;bysecond=0');

end;

/

begin

dbms_scheduler.create_schedule(schedule_name => 's6',repeat_interval=>'freq=daily;

byhour=18;byminute=30;bysecond=0');

end;

/

begin

dbms_scheduler.create_window(

window_name=>'w1',resource_plan=>'DAYTIME',schedule_name=>'S5',

duration=>'0 9:00:0',window_priority=>'HIGH',comments=>'');

end;

/

duration 选项决定窗口的持续时间（<天数> <小时数>:<分钟数>:<秒数>）

window_priority 选项决定多窗口交迭时哪个窗口有效（同一时间只有一个窗口有效）

begin

dbms_scheduler.create_window(

window_name=>'w2',resource_plan=>'NIGHTTIME',schedule_name=>'S6',

duration=>'0 15:00:0',window_priority=>'HIGH',comments=>'');

end;

/

col window_name format a20

col resource_plan format a15

col schedule_name format a20

第 45 页

col duration format a20

col comments format a30

select window_name,resource_plan,schedule_name,schedule_type,duration,window_priority,enabled,active,comments

from dba_scheduler_windows where window_name like 'W_';

WINDOW_NAME RESOURCE_PLAN SCHEDULE_NAME SCHEDULE DURATION WIND ENABL ACTIV COMMENTS

W1 DAYTIME S5 NAMED +000 09:00:00 HIGH TRUE FALSE

W2 NIGHTTIME S6 NAMED +000 15:00:00 HIGH TRUE FALSE

列出窗口信息

col window_name format a15

col operation format a15

col status format a15

col user_name format a15

select log_date,window_name,operation,status,user_name from dba_scheduler_window_log;

LOG_DATE WINDOW_NAME OPERATION STATUS USER_NAME

18-7 月 -08 09.25.03.531000 上午 +08:00 W1 OPEN

18-7 月 -08 10.36.10.062000 上午 +08:00 W2 OPEN

18-7 月 -08 09.25.03.531000 上午 +08:00 W1 CREATE SYS

18-7 月 -08 09.58.50.687000 上午 +08:00 W2 CREATE SYS

18-7 月 -08 10.36.09.968000 上午 +08:00 W1 CLOSE

18-7 月 -08 10.36.10.062000 上午 +08:00 W1 DROP SYS

18-7 月 -08 10.36.16.812000 上午 +08:00 W2 CLOSE

18-7 月 -08 10.36.16.812000 上午 +08:00 W2 DROP SYS

18-7 月 -08 10.37.16.640000 上午 +08:00 W1 CREATE SYS

18-7 月 -08 10.37.35.937000 上午 +08:00 W2 CREATE SYS

18-7 月 -08 10.39.36.859000 上午 +08:00 W2 DROP SYS

LOG_DATE WINDOW_NAME OPERATION STATUS USER_NAME

18-7 月 -08 10.39.50.468000 上午 +08:00 W2 CREATE SYS

窗口操作日志（详细日志看 DBA_SCHEDULER_WINDOW_DETAILS）

注：创建窗口需要 MANAGE SCHEDULER 权限，总是创建在 SYS 模式内；窗口主要用于资源计划的自动切换

作业

begin

dbms_scheduler.create_job(

job_name=>'j1',program_name=>'PROG_INSERT_T',schedule_name=>'S1',

job_class=>'JC1',enabled=>true,auto_drop=>false,comments=>'');

end;

/

创建一作业，使用已存在的程序、调度和作业类

enabled 选项缺省为 false，true 时创建完作业就启动

auto_drop 选项，缺省为 true，说明当到达结束时间，或到达 max_runs（SET_ATTRIBUTE 设置），或只运行一次时系统自动删除作业

begin

dbms_scheduler.create_job(

job_name=>'J2',program_name=>'PROG_PRO_INSERT_T',schedule_name=>'S2',

job_class=>'JC2',enabled=>false,auto_drop=>false,comments=>'');

dbms_scheduler.set_job_argument_value(

job_name=>'J2',argument_name=>'P_NUM',argument_value=>6);

dbms_scheduler.enable('J2');

end;

/

begin

dbms_scheduler.create_job(

job_name=>'j3',program_name=>'PROG_PING',schedule_name=>'S1',

enabled=>true,auto_drop=>false,comments=>'');

end;

/

col job_name format a15

col program_name format a20

col schedule_name format a15

col job_class format a20

col start_date format a40

col state format a10

select job_name,program_name,schedule_name,start_date,job_class,enabled,auto_drop,restartable,state

from dba_scheduler_jobs where job_name in ('J1','J2','J3');

JOB_NAME PROGRAM_NAME SCHEDULE_NAME START_DATE JOB_CLASS ENABL AUTO_ RESTA STATE

J1 PROG_INSERT_T S1 18-7 月 -08 01.16.21.700000 下午 +08:00 JC1 TRUE FALSE FALSE SCHEDULED

J2 PROG_PRO_INSERT_T S2 18-7 月 -08 04.15.40.700000 下午 +08:00 JC2 TRUE FALSE FALSE SCHEDULED

第 46 页

J3 PROG_PING S1 18-7 月 -08 01.59.35.300000 下午 +08:00 DEFAULT_JOB_CLASS TRUE FALSE FALSE SCHEDULED

列出作业信息基本信息

col job_name format a10

col last_start_date format a40

col next_run_date format a40

col last_run_duration format a30

select job_name,job_priority,run_count,failure_count,retry_count,last_start_date,last_run_duration,

next_run_date,logging_level

from dba_scheduler_jobs where job_name in ('J1','J2','J3');

JOB_NAME JOB_PRIORITY RUN_COUNT FAILURE_COUNT RETRY_COUNT LAST_START_DATE LAST_RUN_DURATION

NEXT_RUN_DATE LOGG

J1 3 2 0 0 18-7 月 -08 03.16.21.796000 下午 +08:00 +000000000 00:00:00.032000

18-7 月 -08 05.16.21.000000 下午 +08:00 RUNS

J2 3 1 0 0 18-7 月 -08 04.15.40.828000 下午 +08:00 +000000000 00:00:00.031000

18-7 月 -08 05.15.40.800000 下午 +08:00 RUNS

J3 3 2 0 0 18-7 月 -08 03.59.35.406000 下午 +08:00 +000000000 00:00:03.344000

18-7 月 -08 05.59.35.000000 下午 +08:00 RUNS

列出了作业优先级、运行次数、失败次数、重试次数、最后一次启动作业时间、最后一次作业完成所花时间、下次启动时间、日志方式等信息

col log_date format a50

col job_name format a20

select log_date,job_name,operation,status from dba_scheduler_job_log where job_name in ('J1','J2','J3')

order by job_name;

LOG_DATE JOB_NAME OPERATION STATUS

18-7 月 -08 03.16.21.828000 下午 +08:00 J1 RUN SUCCEEDED

18-7 月 -08 01.16.21.812000 下午 +08:00 J1 RUN SUCCEEDED

18-7 月 -08 02.15.54.750000 下午 +08:00 J2 RUN FAILED

18-7 月 -08 03.15.52.359000 下午 +08:00 J2 RUN FAILED

18-7 月 -08 03.33.08.906000 下午 +08:00 J2 RUN FAILED

18-7 月 -08 03.37.24.250000 下午 +08:00 J2 RUN SUCCEEDED

18-7 月 -08 04.15.40.859000 下午 +08:00 J2 RUN SUCCEEDED

18-7 月 -08 01.59.38.625000 下午 +08:00 J3 RUN SUCCEEDED

18-7 月 -08 01.56.26.828000 下午 +08:00 J3 RUN STOPPED

18-7 月 -08 03.59.38.750000 下午 +08:00 J3 RUN SUCCEEDED

列出了作业日志

权限

select distinct privilege from dba_sys_privs

where privilege like '%JOB%' or privilege like '%PROGRAM%'

or privilege like '%SCHEDULE%' or privilege like '%CLASS%';

PRIVILEGE

CREATE JOB

EXECUTE ANY CLASS

CREATE ANY JOB

EXECUTE ANY PROGRAM

MANAGE SCHEDULER

CREATE EXTERNAL JOB

相关权限，除了以上还有：

u EXECUTE ON

u ALTER ON

u ALL ON