DBMS_SCHEDULER测试

操作系统 windows xp, oracle 10.2.0.1

参考文档：
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_sched.htm#1010185
http://space.itpub.net/32496/viewspace-117513
http://haochunpeng.itpub.net/post/385/43335

+-------------------------------+
|     PL/SQL Proc Test 1   |
+-------------------------------+

SQL> --创建Procedure SQL> create or replace procedure p_insertSysTime   2  is   3  begin   4  insert into scott.test values (0,to_char(sysdate,'hh24:mi'));   5  commit;   6  end p_insertSysTime;   7  / 过程已创建。 --确认该用户已经有CREATE JOB权限。 --GRANT CREATE JOB TO SCOTT; --创建Program: 理解为定义要做什么 BEGIN dbms_scheduler.create_program( program_name => 'PrgInsertTime', program_type => 'stored_procedure',  --PL/SQL program_action => 'p_insertSysTime', enabled => TRUE); END; / PL/SQL 过程已成功完成。 --创建schedule: 理解为定义什么时候做; 此时未定义Start_date BEGIN dbms_scheduler.create_schedule(    schedule_name  => 'SCHEDULE_1_MIN',    repeat_interval => 'freq=MINUTELY;interval=1',    comments     => 'every minute'); end; / PL/SQL 过程已成功完成。 --创建job,调用之前创建的program和schedule begin dbms_scheduler.create_job( job_name => 'JobInsertTime', program_name => 'PrgInsertTime', schedule_name => 'SCHEDULE_1_MIN', enabled =>true);  --default: false end; / PL/SQL 过程已成功完成。 --此时这个job已经开始运行了！ SQL> select * from test;         ID NAME ---------- ----------          0 14:08          0 14:09 SQL> SELECT job_name,program_name,schedule_name,start_date,repeat_interval,   2  end_date,state,run_count,NEXT_RUN_DATE   3  from user_scheduler_jobs; JOB_NAME             PROGRAM_NAME    SCHEDULE_NAME   START_DATE -------------------- --------------- --------------- -------------------- REPEAT_INT END_DATE             STATE       RUN_COUNT NEXT_RUN_D ---------- -------------------- ---------- ---------- ---------- JOBINSERTTIME        PRGINSERTTIME   SCHEDULE_1_MIN  14-5月 -08 02.17.24.                                                      000000 下午 +08:00                                 SCHEDULED          10 14-5月 -08                                                        02.17.24.                                                       000000 下                                                       午 +08:00 SQL> begin   2      dbms_scheduler.drop_job(job_name => 'JOBINSERTTIME',force => TRUE);   3  end;   4  / PL/SQL 过程已成功完成。 SQL> SQL> SELECT job_name,program_name,schedule_name,start_date,repeat_interval,   2  end_date,state,run_count,NEXT_RUN_DATE   3  from user_scheduler_jobs; 未选定行

+-------------------------------+
|     PL/SQL Proc Test 2   |
+-------------------------------+

begin dbms_scheduler.drop_schedule( schedule_name => 'SCHEDULE_1_MIN'); end; / BEGIN dbms_scheduler.create_schedule(    schedule_name  => 'SCHEDULE_1_MIN',    start_date    => to_date('200805141430','yyyymmddhh24mi'),    repeat_interval => 'freq=MINUTELY;interval=1',    comments     => 'every minute'); end; / begin dbms_scheduler.create_job( job_name => 'JobInsertTime', program_name => 'PrgInsertTime', schedule_name => 'SCHEDULE_1_MIN', enabled =>true);  --default: false end; / begin     dbms_scheduler.run_job('JobInsertTime',TRUE); -- true代表同步执行 end; / --user_scheduler_jobs显示JOBINSERTTIME 尚未运行，run_count为0，但实际test表已有数据进入，这条数据其实是run_job运行时插入的。 --这一步我的理解是run_job类似于toad中的execute immediate，并不是让我们定义的program按照scheduler来连续执行，而是单独执行一次。 SQL> SELECT job_name,program_name,schedule_name,start_date,repeat_interval,   2  end_date,state,run_count,NEXT_RUN_DATE   3  from user_scheduler_jobs; JOB_NAME             PROGRAM_NAME    SCHEDULE_NAME   START_DATE -------------------- --------------- --------------- -------------------- REPEAT_INT END_DATE             STATE       RUN_COUNT NEXT_RUN_D ---------- -------------------- ---------- ---------- ---------- JOBINSERTTIME        PRGINSERTTIME   SCHEDULE_1_MIN  14-5月 -08 02.30.00.                                                      000000 下午 +08:00                                 SCHEDULED           0 14-5月 -08                                                        02.30.00.                                                       000000 下                                                       午 +08:00                                                       SQL> select * from test;         ID NAME ---------- ----------          0 14:27          --14:30以后再查看，scheduler已经从14:30开始连续执行 SQL> select to_char(sysdate,'hh24:mi') from dual; TO_CH ----- 14:32 SQL> select * from test;         ID NAME ---------- ----------          0 14:32          0 14:30          0 14:31          0 14:27          0 14:28 SQL> SELECT job_name,program_name,schedule_name,start_date,repeat_interval,   2  end_date,state,run_count,NEXT_RUN_DATE   3  from user_scheduler_jobs; JOB_NAME             PROGRAM_NAME    SCHEDULE_NAME   START_DATE -------------------- --------------- --------------- -------------------- REPEAT_INT END_DATE             STATE       RUN_COUNT NEXT_RUN_D ---------- -------------------- ---------- ---------- ---------- JOBINSERTTIME        PRGINSERTTIME   SCHEDULE_1_MIN  14-5月 -08 02.30.00.                                                      000000 下午 +08:00                                 SCHEDULED           3 14-5月 -08                                                        02.33.00.                                                       000000 下                                                       午 +08:00   begin     dbms_scheduler.stop_job(job_name => 'JobInsertTime',force => TRUE); end; / begin * 第 1 行出现错误: ORA-27486: 权限不足 ORA-06512: 在 "SYS.DBMS_ISCHED", line 164 ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 483 ORA-06512: 在 line 2 --grant manage scheduler to scott; --stop_job需要有manage scheduler权限才能够执行 begin     dbms_scheduler.stop_job(job_name => 'JobInsertTime',force => TRUE); end; / begin * 第 1 行出现错误: ORA-27366: 作业 "SCOTT.JOBINSERTTIME" 不在运行 ORA-06512: 在 "SYS.DBMS_ISCHED", line 164 ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 483 ORA-06512: 在 line 2 "stop_job" command just only stop the running job ,if the job is not running or it has been stoped ,it not to be stoped any more,can you understand? if you do not want the job run any more ,you can use dbms_scheduler.disable(jname => 'your job name',force => 'TRUE' ) to stop the job --想了个办法，修改了attribute的max_runs参数，把这个job停了下来 SQL> SELECT job_name,program_name,schedule_name,start_date,repeat_interval,   2  end_date,state,run_count,NEXT_RUN_DATE   3  from user_scheduler_jobs; JOB_NAME             PROGRAM_NAME    SCHEDULE_NAME   START_DATE -------------------- --------------- --------------- -------------------- REPEAT_INT END_DATE             STATE       RUN_COUNT NEXT_RUN_D ---------- -------------------- ---------- ---------- ---------- JOBINSERTTIME        PRGINSERTTIME   SCHEDULE_1_MIN  14-5月 -08 02.30.00.                                                      000000 下午 +08:00                                 SCHEDULED           5 14-5月 -08                                                        02.35.00.                                                       000000 下                                                       午 +08:00 begin DBMS_SCHEDULER.SET_ATTRIBUTE (  name =>'JOBINSERTTIME',  attribute => 'max_runs',  value  => 7); end; / SQL> SELECT job_name,program_name,schedule_name,start_date,repeat_interval,   2  end_date,state,run_count,NEXT_RUN_DATE   3  from user_scheduler_jobs; 未选定行 --此时job应该已经结束，再drop_job报错 begin     dbms_scheduler.drop_job(job_name => 'JOBINSERTTIME',force => TRUE); end; / begin * 第 1 行出现错误: ORA-27475: "SCOTT.JOBINSERTTIME" 必须为 job ORA-06512: 在 "SYS.DBMS_ISCHED", line 178 ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 544 ORA-06512: 在 line 2

+-----------------------------+
|     Shell Script. Test    |
+-----------------------------+

总结：
(1)Windows平台，在OracleJobSchedulerORA10G服务未启动前，同样的脚本sys用户可以执行，而scott用户无法执行
(2)在OracleJobSchedulerORA10G服务启动后，scott也可以执行bat脚本. OracleJobSchedulerSID Service是用于支持external job的
(3)scott用户要有CREATE EXTERNAL JOB的权限才能够创建type为EXECUTABLE的program
(4)oracle是以os的nobody身份运行job的.如果program的脚本,nobody没有权限执行的话.那么job会运行失败的.

BEGIN dbms_scheduler.create_program( program_name => 'cpFile', program_type => 'EXECUTABLE', program_action => 'C:\WINDOWS\system32\cmd.exe /c d:\\ttt.bat',　--直接调用d:\ttt.bat亦可 enabled => TRUE); END; / begin dbms_scheduler.create_job( job_name => 'JobCpFile', program_name => 'cpFile', schedule_name => 'SCHEDULE_1_MIN', enabled =>true); end; / begin dbms_scheduler.stop_job(job_name=>'JobCpFile'); end; / begin * 第 1 行出现错误: ORA-27366: 作业 "SCOTT.JOBCPFILE" 不在运行 ORA-06512: 在 "SYS.DBMS_ISCHED", line 164 ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 483 ORA-06512: 在 line 2 begin dbms_scheduler.drop_job(job_name=>'JobCpFile'); end; / SQL> SELECT job_name,program_name,schedule_name,start_date,repeat_interval,   2  end_date,state,run_count,NEXT_RUN_DATE   3  from user_scheduler_jobs; 未选定行

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/55472/viewspace-266514/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-266514/