[bbk5158]第55集-第6章 - 用scheduler自动化 03

/****************************************************/

创建一张空表，编写一个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;

/