oracle查询job维护窗口期间,Oracle Job维护

1. Job的创建

-- 创建JOB

BEGIN

-- Job defined entirely by the CREATE JOB procedure.

DBMS_SCHEDULER.create_job (

job_name => ‘test_full_job_definition‘,

job_type => ‘PLSQL_BLOCK‘,

job_action => ‘BEGIN my_job_proc(‘‘CREATE_PROGRAM (BLOCK)‘‘); END;‘,

start_date => SYSTIMESTAMP,

repeat_interval => ‘freq=hourly; byminute=0‘,

end_date => NULL,

enabled => TRUE,

comments => ‘Job defined entirely by the CREATE JOB procedure.‘);

END;

/

BEGIN

-- Job defined by an existing program and schedule.

DBMS_SCHEDULER.create_job (

job_name => ‘test_prog_sched_job_definition‘,

program_name => ‘test_plsql_block_prog‘,

schedule_name => ‘test_hourly_schedule‘,

enabled => TRUE,

comments => ‘Job defined by an existing program and schedule.‘);

END;

/

BEGIN

-- Job defined by an existing program and inline schedule.

DBMS_SCHEDULER.create_job (

job_name => ‘test_prog_job_definition‘,

program_name => ‘test_plsql_block_prog‘,

start_date => SYSTIMESTAMP,

repeat_interval => ‘freq=hourly; byminute=0‘,

end_date => NULL,

enabled => TRUE,

comments => ‘Job defined by existing program and inline schedule.‘);

END;

/

BEGIN

-- Job defined by existing schedule and inline program.

DBMS_SCHEDULER.create_job (

job_name => ‘test_sched_job_definition‘,

schedule_name => ‘test_hourly_schedule‘,

job_type => ‘PLSQL_BLOCK‘,

job_action => ‘BEGIN my_job_proc(‘‘CREATE_PROGRAM (BLOCK)‘‘); END;‘,

enabled => TRUE,

comments => ‘Job defined by existing schedule and inline program.‘);

END;

/

2.  Job 查询，启动,运行，停止,删除,`修改参数`

-- 查询job

select owner, job_name, state from dba_scheduler_jobs;

select * from user_scheduler_jobs

SELECT * FROM User_Scheduler_Job_Run_Details;

-- 启用job

begin

dbms_scheduler.enable(‘job_name‘);

end;

/

-- 运行job

begin

dbms_scheduler.run_job(‘job_name‘,TRUE); -- true代表同步执行

end;

/

-- 停止job(不太好用)

begin

dbms_scheduler.stop_job(job_name => ‘job_name‘,force => TRUE);

end;

/

-- 删除job(对停job来说好用)

begin

dbms_scheduler.drop_job(job_name => ‘job_name‘,force => TRUE);

end;

/

-- 删除job(2)

begin

dbms_scheduler.drop_job(‘job_name‘);

end;

-- 修改job参数

begin

dbms_scheduler.set_attribute(‘job_name‘,‘属性字段‘,‘属性内容‘);

END;

/

3. Job的repeat_interval参数设置

-- calendar expression

repeat_interval=>‘Freq=Secondly;Interval=30‘; -- 每4小时

repeat_interval=>‘FREQ=HOURLY ; INTERVAL=4‘; -- 每4小时

repeat_interval=>‘FREQ=DAILY‘; -- 每天重复一次

repeat_interval=>‘FREQ=MINUTELY ; INTERVAL=15‘; -- 每15分钟重复一次

repeat_interval=>‘FREQ=YEARLY ;

BYMONTH=MAR,JUN,SEP,DEC;

BYMONTHDAY=15‘; -- 每年5,7,9,12月的15号重复一次

-- pl/sql expression

repeat_interval=>‘SYSDATE + 26/24‘; -- 每36小时重复一次

repeat_interval=>‘SYSDATE + 1‘; --每天重复一次

repeat_interval=>‘SYSDATE + 15/(24*60)‘; -- 每15分钟重复一次