[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.
1、create table
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.
2、create procedure
BEGIN
        DBMS_SCHEDULER.CREATE_PROGRAM(
                program_name => 'prog1',
                program_action => 'U2.proc1',
                program_type => 'STORED_PROCEDURE',
                enabled => TRUE
        );
END;
/
~
4、create program
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;
/
create scheduler
BEGIN
        DBMS_SCHEDULER.CREATE_JOB(
                job_name => 'U2.job1',
                program_name => 'U2.prog1',
                schedule_name => 'U2.schedule1'
        );
END;
/
6、create job
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
7、monitor job
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
8、enable job
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.
9、monitor job

如果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;

/

 

posted @ 2013-05-28 22:49  ArcerZhang  阅读(167)  评论(0编辑  收藏  举报