《oracle通过job定时执行任务》由会员分享，可在线阅读，更多相关《oracle通过job定时执行任务（8页珍藏版）》请在金锄头文库上搜索。
1、oracle 通过 job 定时执行任务 2010 年 12 月 11 日 星期六 17:29在项目中，经常会遇到需要定时完成的任务，比如定时更新数据，定义统计数据生成报表等等，其实这些事情都可以使用 Oracle 的 Job 来完成。下面考试大就结合我们实验室项目实际，简单介绍一下在 Oracle 数据库中通过 Job 完成自动创建表的方法。整个过程总共分为两步。虽然整个过程都非常简单，但是对于初学 Oracle 的生手还是有很多地方需要注意的。首先介绍一下，创建该 JOB 的背景，因为每天更新的直播和点播节目信息比较多，为了方便处理，需要每天创建一张表来记录更新的节目信息，当前数据库中已经
2、有一张tbl_programme 的表，每天创建的表的字段需要同 tbl_programme 保持一致，每天新创建的表的名称格式为 tbl_programme_日期（例如： tbl_programme_20090214）规定每天晚上 1 点钟生成该天的新表。第一步：创建一个执行创建操作的存储过程在这一步首先要解决的问题就是构造表名。在 Oracle 中格式话输出时间可以用to_char 函数来处理，例如：SQL select to_char(sysdate, yyyy/mm/dd hh24:mi:ss) from dual;TO_CHAR(SYSDATE,YYYY/MM/DDHH2-2009/
3、02/14 17:22:41以上 SQL 格式化输出了时间，要得到我们所需要的格式直接修改一下 SQL 即可SQL select to_char(sysdate, yyyymmdd) from dual;TO_CHAR(SYSDATE,YYYYMMDD)-20090214得到时间格式字符串后我们就可以将表名的前缀和时间连接在一起形成完整的表名。这里需要注意，在 Oracle 中链接两个字符串需要使用 |符号，而在 Sql Server 中直接使用+ 号就可以了，因为我以前一直在 Sql Server 下编程，好久都没编写 Oracle 的SQL 所以费了很大的功夫才发现这个问题。完整的 Sql
4、 就是SQL select tbl_programme_ | to_char(sysdate, yyyymmdd) from dual;TBL_PROGRAMME_|TO_CHAR(SYSD-tbl_programme_20090214接下来就是创建表的代码了，因为新表需要 tbl_programme 保持一致，所以直接CTAS 来创建表那是非常适合的了，代码如下：Create table tablename as select * from tbl_programme如果需要指定一个 TableSpace 则将该 SQL 做适当修改：Create table tablename tables
5、pace p2p as select * from tbl_programme所以整个创建存储过程的 SQL 就是create or replace procedure sp_createtab_tbl_programmeAuthid Current_Userastabname varchar(200);beginselect TBL_PROGRAMME_ | to_char(sysdate, yyyymmdd) into tabname from dual;-create table tabname as select * from tbl_programme where 1 != 1;ex
6、ecute immediate create table | tabname | tablespace p2p as select * from tbl_programme where 1 != 1;commit;end;/这里还需要注意一下在 Oracle 里面如果要对一个变量赋值的话有两种方式：（1） 使用：= 进行赋值（2） 使用 select xjkxj into 变量名称 from tabname另外，在存储过程中定义变量的时候一般放在 as/is 后 begin 前面。在存储过程一般是不能直接使用 create table，truncate table 这类似的语句的，如果要使用这
7、些语句必须使用 excute immediate + 所要执行的 sql 语句来实现。注意上面用红色标志的语句：Authid Current_User这个语句比较重要，如果我们在创建存储过程的时候不添加这条语句执行该存储过程将不会成功，原因是默认情况向存储过程是没有 Create table 等权限的，即使当前用户有 DBA 的权限也不行，如果存储过程中存在创建表的操作，可以有以下两种方式来解决该问题。（1） 显示的赋予该用户 Create table 的权限，grant create table to user.（2） 在存储过程中使用 Authid Current_User 标识使用当前用
8、户的权限。第二步：创建 JOB创建 JOB 就比较简单了，下面就是创建 JOB 的代码每天晚上 1 电 job 启动一次，执行 sp_createtab_tbl_programme 存储过程。VARIABLE testjobid number;beginsys.dbms_job.submit(:testjobid,sp_createtab_tbl_programme;,trunc(sysdate+1)+1/24,trunc(sysdate+1)+1/24);commit;end;/这里需要注意的是，在 submit 方法的前面一定要先定义 job 这个变量，另外，submit 方法的第二个参数
9、是一个存储过程的名，记得在后面添加“：”号，在 next_date是一个时间类型变量而不是一个字符串，所以需要注意不要把它当成字符串，不需要对该参数加引号。最后一个参数 interval 是一个字符串类型，记得添加引号。最常见的错误如下图所示：ORA-01008： not all variables bound 就是没有定义变量的意思。一定记的在使用 submit 方法时定义 jobid 变量。下面是常有的设置 Interval 的方法：2 每天固定时间运行，比如早上 8：10 分钟：Trunc （Sysdate+1） + 8/24 每天：trunc（sysdate+1） 每周：trunc（s
10、ysdate+7） 每月：trunc（sysdate+30） 每个星期日：next_day（trunc（sysdate ），SUNDAY） 每天 6 点：trunc （sysdate+1）+6/24 半个小时：sysdate+30/1440需要用到的完整 SQL 如下：- Export file for user P2P - Created by Administrator on 2009-2-14, 15:45:18 -spool gjgdp2p(v1.3).logpromptprompt Creating procedure SP_CREATETAB_TBL_PROGRAMMEprompt
11、 =promptcreate or replace procedure sp_createtab_tbl_programmeAuthid Current_Userastabname varchar(200);beginselect TBL_PROGRAMME_ | to_char(sysdate, yyyymmdd) into tabname from dual;-create table tabname as select * from tbl_programme where 1 != 1;execute immediate create table | tabname | tablespa
12、ce p2p as select * from tbl_programme where 1 != 1;commit;end;/VARIABLE testjobid number;beginsys.dbms_job.submit(:testjobid,sp_createtab_tbl_programme;,trunc(sysdate+1)+1/24,trunc(sysdate+1)+1/24);commit;end;/spool off第三步：异常情况处理JOB 不能运行情况处理1. 先来了解一下 JOB 的参数说明：与 job 相关的参数一个是job_queue_processes，这个是运行
13、 JOB 时候所起的进程数，当然系统里面 JOB 大于这个数值后，就会有排队等候的，最小值是 0，表示不运行 JOB，最大值是 36，在 OS上对应的进程时 SNPn，9i 以后 OS 上管理 JOB 的进程叫 CJQn.可以使用下面这个 SQL确定目前有几个 SNP/CJQ 在运行。select * from v$bgprocess，这个 paddr 不为空的 snp/cjq 进程就是目前空闲的进程，有的表示正在工作的进程。另外一个是 job_queue_interval，范围在 13600 之间，单位是秒，这个是唤醒 JOB 的 process，因为每次 snp 运行完他就休息了，需要定期
14、唤醒他，这个值不能太小，太小会影响数据库的性能。2. 诊断：先确定上面这两个参数设置是否正确，特别是第一个参数，设置为 0 了，所有 JOB 就不会跑，确认无误后，我们继续向下。3. 使用下面的 SQL 察看 JOB 的的 broken，last_date 和 next_date，last_date是指最近一次 job 运行成功的结束时间， next_date 是根据设置的频率计算的下次执行时间，根据这个信息就可以判断 JOB 上次是否正常，还可以判断下次的时间对不对，SQL如下：select * from dba_jobs有时候我们发现他的 next_date 是 4000 年 1 月 1
15、日，说明 job 要不就是在running，要不就是状态是 break（broken=Y），如果发现 JOB 的 broken 值为 Y，找用户了解一下，确定该 JOB 是否可以 broken，如果不能 broken，那就把 broken 值修改成 N，修改再使用上面的 SQL 察看就发现他的 last_date 已经变了，JOB 即可正常运行，修改 broken 状态的 SQL 如下：declareBEGINDBMS_JOB.BROKEN（ ，FALSE）；END；4. 使用下面的 SQL 查询是否 JOB 还在 Runningselect * from dba_jobs_running如果发现 JOB 已经 Run 了很久了还没有结束，就要查原因了。一般的 JOB running时会锁定相关的相关的资源，可以查看一下 v$access 和 v$locked_object 这两个view，如果发现其他进程锁定了与 JOB 相关的 Object，包括PKG/Function/Procedure/Table 等资源，那
