/****************************************************/
创建一张空表，编写一个Job.每隔1分钟向表中插入一条数据
实验步骤
1、create table
create table log（user_name varchar2(10),user_date date）;
2、create procedure --- pl/sql
3、grant (grant create job or create any job)
4、create program
5、create scheduler
6、create job
7、monitor job
8、enable job (dbms_scheduler.enable('job_name'))
/****************************************************/
SQL> create table log( 2 user_name varchar2(10), 3 user_date date 4 ); Table created.
SQL> create or replace procedure proc1 2 is 3 begin 4 insert into log(user_name,user_date) values(user,sysdate); 5 commit; 6 end; 7 / Procedure created.
BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'prog1', program_action => 'U2.proc1', program_type => 'STORED_PROCEDURE', enabled => TRUE ); END; / ~
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'schedule1', start_date => SYSTIMESTAMP, end_date => SYSTIMESTAMP + 1, repeat_interval => 'FREQ=MINUTELY;INTERVAL=1', comments => 'Every MINUTELY' ); END; /
BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'U2.job1', program_name => 'U2.prog1', schedule_name => 'U2.schedule1' ); END; /
SQL> select job_name,status,error#,run_duration from user_scheduler_job_run_details; no rows selected SQL> select job_name,status,error#,run_duration from user_scheduler_job_run_details; no rows selected SQL> select job_name,enabled from user_scheduler_jobs; JOB_NAME ENABL ------------------------------ ----- ADD_DEPT_JOB FALSE JOB1 FALSE
SQL> select job_name,enabled from user_scheduler_jobs; JOB_NAME ENABL ------------------------------ ----- ADD_DEPT_JOB FALSE JOB1 FALSE SQL> begin 2 dbms_scheduler.enable('JOB1'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> begin 2 dbms_scheduler.enable('ADD_DEPT_JOB'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select job_name,enabled from user_scheduler_jobs; JOB_NAME ENABL ------------------------------ ----- ADD_DEPT_JOB TRUE JOB1 TRUE
SQL> l 1* select job_name,status,error#,run_duration from user_scheduler_job_run_details SQL> / JOB_NAME STATUS ERROR# RUN_DURATION -------------------- -------------------- ---------- ------------------------------ JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 JOB1 SUCCEEDED 0 +000 00:00:00 ADD_DEPT_JOB STOPPED 0 +000 00:00:03 7 rows selected.
如果scheduler执行失败，如何排查错误?
如果不想让一个scheduler再执行，如何操作?
begin dbms_scheduler.disable('job1'); end; /
如何删除一个JOB？
begin dbms_scheduler.drop_job( job_name => 'job1'; ); end; /
如何删除一个scheduler？
begin dbms_scheduler.drop_schedule( schedule_name => 'schedule1'; ); end; /
如何删除一个program?
begin dbms_scheduler.drop_program( program_name => 'prog1'; ); end; /