使用dbms_job包创建Oracle定时任务,dbms_job创建oracle
在Oracle的包里面,有一个名字叫做DBMS_JOB的包,它的作用是安排和管理作业队列。通过作业队列,可以让Oracle数据库定期执行特定的任务。当使用DBMS_JOB管理作业的时候,必须确保设置了初始化参数JOB_QUEUE_PROCESSES(不能为0)。
1、 SUBMIT
该过程用于建立一个新的作业,当建立作业的时候,需要通过设置相应的参数来告诉Oracle要执行的内容,要执行的时间,要执行任务的间隔。如下格式:
DBMS_JOB.SUBMIT( JOB OUT BINARY_INTERGER, WHAT IN VARCHAR2, NEXT_DATE IN DATE DEFAULT SYSDATE, INTERVAL IN VARCHAR2 DEFAULT ‘NULL’, NO_PARSE IN BOOLEAN DEFAULT FALSE, INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE, FORCE IN BOOLEAN DEFAULT FALSE );
建立Oracle作业的例子:
--如果表存在就删除,然后创建一个新表 drop table testjob cascade constraints; create table testjob( id number constraint testjob_id_pk primary key, name varchar2(30) ); --如果序列存在就删除,然后创建一个序列 drop sequence testjob_id_seq; create sequence testjob_id_seq; --创建一个序列,每一次向表中插入一条数据,并且表中的id字段值使用序列指定 create or replace procedure insert_job is begin insert into testjob values(testjob_id_seq.nextval,'test'||testjob_id_seq.currval); commit; end; / declare jobno binary_integer; begin --提交,操作的时间间隔设置为1分钟 dbms_job.submit(jobno,'insert_job();',sysdate,'sysdate+1/(24*60)'); --打印序列号 dbms_output.put_line('jobno='||jobno); --运行 dbms_job.run(jobno); end; /
interval参数值
2、 REMOVE
这个过程的作用是用于删除作业队列当中的特定的作业,它的语法如下:
DBMS_JOB.REMOVE(JOB IN BINARY_INTEGER);
下面是一个删除作业的例子:
首先查看DBA_JOBS表,看表里面有哪些任务正在执行着?
SELECT * FROM DBA_JOBS;
可以看到里面的JOB就是我们要删除的作业的编号,LOG_USER是创建该任务的人。
SQL> EXEC DBMS_JOB.REMOVE(467); SQL>COMMIT;
这样就能把已经建立的作业删除了。
3、 CHANGE
该过程改变与作业相关的所有的信息,其中包括作业的操作内容,作业运行的时间以及运行时间间隔信息等等。语法如下:
DBMS_JOB.CHANGE( JOB IN BINARY_INTEGER, WHAT IN VARCHAR2, NEXT_DATE, INTERVAL IN VARCHAR2, INSTANCE IN BINARY_INTEGER DEFAULT NULL, FORCE IN BOOLEAN DEFAULT FALSE );
例子:
SQL>EXEC DBMS_JOB.CHANGE(2,NULL,NULL,’SYSDATE+2’); SQL>COMMIT;
4、 WHAT
WHAT用来改变作业要执行的操作,例如:
SQL>EXEC DBMS_JOB.WHAT(268,’GETGX_AC01;’);
5、 NEXT_DATE
用来改变作业的下次运行日期
SQL>EXEC DBMS_JOB.NEXT_DATE(‘478’,’SYSDATE+2’);
6、 INTERVAL
该过程用来改变作业的运行时间间隔,下面的运行时间间隔修改为每分钟执行一次:
SQL>exec dbms_job.interval(478,’SYSDATE+1/24/60
7、 BROKEN
该过程用于给该作业打上中断标志,可以在DBA_JOBS表里面观察该作业的BROKEN标志知否为中断。例子:
SQL>EXEC DBMS_JOB.BROKEN(478,TRUE); SQL>COMMIT;
8、 RUN
该过程用来执行该作业,例子:
SQL>EXEC DBMS_RN(478); SQL>COMMIT;
常见问题:
1、 如何停止一个作业?
SQL>DBMS_JOBS.BROKEN(2,TRUE); SQL>COMMIT;
这里务必要提交哈!
2、 如何启动一个作业?
SQL>DBMS_JOBS.BROKEN(2,FALSE); SQL>COMMIT;
这里也务必要提交,否则就没有效果
3、 前面提到的JOB_QUEUE_PROCESSES在什么地方设置?
ALTER SYSTEM SET job_queue_processes=39 SCOPE=SPFILE;
这个SQL的执行是需要具备相应的权限的。
4、 如何计算一个过程运行的时间(DATE和TimeStamp)?
可以在过程的开始设置一个时间,然后在过程的结尾处设置一个时间,然后两个时间的时间差可以计算出该过程运行的时间。通过实践发现使用SYSESTAMP来计算时间比较准确一些:
SELECT to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff4') FROM dual;