1.目的：
|
写job 每三个小时 为单元，头一个三个小时 从 1到100 插入测试表 第二个三个小时 从 101到200插入测试表 第三个三个小时 从 301到400插入测试表. |
2.处理方法：
|
2.1 创建序列. 初始值 1 增量 1 最大值无限制 2.2 创建存储过程, 利用for循环顺序读取序列的nextval 插入 测试表. 2.3 创建job. 定期调用 存储过程, 参数repeat_interval => 'FREQ=minutely;INTERVAL=2', /* every 2 minute */ |
3.创建序列
|
SYS @ prod >CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
Sequence created. |
4.创建存储过程
|
调用序列给测试表 插入数据 SYS @ prod >CREATE OR REPLACE procedure insert_t1 is begin for i in 1..100 loop insert into t1 values(seq1.nextval); commit; end loop; end; /
Procedure created. |
5.测试存储过程
|
SYS @ prod >exec insert_t1;
PL/SQL procedure successfully completed. |
6.创建 job
|
方法一： SYS @ prod >declare begin dbms_scheduler.create_job( job_name =>'job_insert_t1', job_type =>'stored_procedure', job_action =>'sys.insert_t1', start_date =>'05-JUN-14 10.50.00 AM ', repeat_interval => 'FREQ=minutely;INTERVAL=2', /* every minute */ enabled=> true, end_date => '06-JUN-14 10.50.00 AM ', comments => 'My new job'); END; /
PL/SQL procedure successfully completed.
方法二： SYS @ prod >BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name =>'job_insert_t1', job_type =>'STORED_PROCEDURE', job_action =>'insert_t1', repeat_interval =>'FREQ=DAILY; BYHOUR=18 ;BYMINUTE=30', /* every day*/ enabled=> true, comments => 'job'); END;
--每天18/30/00点执行job_insert_t1过程 |
7.运行job
|
SYS @ prod >exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME=>'job_insert_t1',USE_CURRENT_SESSION =>true);
PL/SQL procedure successfully completed. |
8.查看对应包
|
包中分别包含了 运行job、删除job等存储过程 SYS @ prod >desc DBMS_SCHEDULER;
PROCEDURE RUN_JOB Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB_NAME VARCHAR2 IN USE_CURRENT_SESSION BOOLEAN IN DEFAULT
PROCEDURE DROP_JOB Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB_NAME VARCHAR2 IN FORCE BOOLEAN IN DEFAULT |
9.删除job
|
SYS @ prod >exec dbms_scheduler.drop_job(job_name=>'job_insert_t1');
PL/SQL procedure successfully completed. |
10.检查结果
|
检查 测试表中的数据确实在增加 select * from t1 order by n1 desc;
--最大值 1100 但是 查询 dba_jobs、user_jobs、all_jobs 没有任何信息 select * from dba_jobs;
select * from user_jobs;
select * from dba_jobs_running;
只在all_scheduler_job_log 中看到该job 运行的相关信息 select * from ALL_SCHEDULER_JOB_LOG order by log_date desc; |
11.删除 序列、job、存储过程
|
SYS @ prod >drop procedure insert_t1; Procedure dropped. SYS @ prod >drop sequence seq1; Sequence dropped. SYS @ prod >exec dbms_scheduler.drop_job(job_name=>'job_insert_t1'); PL/SQL procedure successfully completed. |