Oracle通过JOB定时创建序列

因为业务需要每月需要增加一个序列，想到了使用job定时创建，每次创建一年的。写此job的过程中遇到一些问题，在此记录一下。第一步：写创建序列（sequence）的存储过程。代码如下：

create or replace procedure pro_create_seq（sequencePrefix in varchar2）
is
strYear varchar2（4）;
strMonth varchar2（6）;
strSql varchar2（500）;
begin
select to_char（sysdate,"yyyy"） into strYear from dual;
for i in 1..12 loop
strMonth:=strYear||lpad（i,2,"0"）;--组装月份，形如：201201
strSql:="create sequence "||sequencePrefix||strMonth||" start with 1 increment by 1 MINVALUE 0 MAXVALUE 99999999999 ";
execute immediate strSql;
end loop;
end pro_create_seq;

创建存储过程成功，但测试该报错了：SQL> exec pro_create_seq（"FACTORY_NOTICE_INFO_DB_"）;

begin pro_create_seq（"FACTORY_NOTICE_INFO_DB_"）; end;

ORA-01031: 权限不足
ORA-06512: 在 "YCPS.PRO_CREATE_SEQ", line 16
ORA-06512: 在 line 2在网上搜索解决方法发现原来是在存储过程中ROLE无效，需要显示授权。即有两种解决方法：1、通过GRANT CREATE SEQUENCE TO YCPS;来显示给用户授权
2、修改存储过程，加入Authid Current_User时存储过程可以使用role权限。我采用了第二种方法，即加入Authid Current_User，修改后的存储过程如下：

create or replace procedure pro_create_seq（sequencePrefix in varchar2）
Authid Current_User --赋予role权限
is
strYear varchar2（4）;
strMonth varchar2（6）;
strSql varchar2（500）;
begin
select to_char（sysdate,"yyyy"） into strYear from dual;--取年份，形如：2012
for i in 1..12 loop
strMonth:=strYear||lpad（i,2,"0"）;--组装月份，形如：201201
strSql:="create sequence "||sequencePrefix||strMonth||" start with 1 increment by 1 MINVALUE 0 MAXVALUE 99999999999 ";
execute immediate strSql;
end loop;
end pro_create_seq;

SQL> exec pro_create_seq（"FACTORY_NOTICE_INFO_DB_"）;PL/SQL procedure successfully completed执行成功。第二步：创建job，定时调用存储过程来创建sequence。

variable job1 number;
begin
dbms_job.submit（:job1,"PRO_CREATE_SEQ（""FACTORY_NOTICE_INFO_DB_""）;PRO_CREATE_SEQ（""FACTORY_NOTICE_INFO_XB_""）;",sysdate,"add_months（TRUNC（SYSDATE,""yyyy""）,12）"）;
commit;
end;

SQL> variable job1 number;
SQL> begin
2   dbms_job.submit（:job1,"PRO_CREATE_SEQ（""FACTORY_NOTICE_INFO_DB_""）;PRO_CREATE_SEQ（""FACTORY_NOTICE_INFO_XB_""）;",sysdate,"add_months（TRUNC（SYSDATE,""yyyy""）,12）"）;
3   commit;
4   end;
5 /

PL/SQL procedure successfully completed
job1
---------
84

SQL> begin
2 dbms_job.run（84）;
3 end;
4 /

begin
dbms_job.run（84）;
end;

ORA-12011: 无法执行 1 作业
ORA-06512: 在 "SYS.DBMS_IJOB", line 637
ORA-06512: 在 "SYS.DBMS_JOB", line 284
ORA-06512: 在 line 3

可见，job创建成功了，但执行出错。查询ORA-12011错误的说明如下：

ORA-12011: execution of string jobs failed
Cause: An error was caught in dbms_ijob.run from one or more jobs which were due to be run.
Action: Look at the alert log for details on which jobs failed and why.

SQL> select value from v$diag_info where name="Diag Trace"; VALUE -------------------------------------------------------------------------------- /u01/app/Oracle/diag/rdbms/ycps/ycps1/trace

上面查找到alert log的路径，日志文件内容如下：Thu Aug 16 14:30:57 2012
Errors in file /u01/app/oracle/diag/rdbms/ycps/ycps1/trace/ycps1_ora_2622362.trc:
ORA-12012: 自动执行作业 84 出错
ORA-01031: 权限不足
ORA-06512: 在 "YCPS.PRO_CREATE_SEQ", line 15
ORA-06512: 在 line 1可见，仍然是权限问题。然而通过exec pro_create_seq（"FACTORY_NOTICE_INFO_DB_"）;调用存储过程却可以执行，很让人费解。后来说需要显式授权，也就是前面提到的方法2，于是测试了一下，竟然成功了：SQL> conn sys/oracle@psdb as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS

SQL> grant create sequence to ycps;

Grant succeeded

SQL> conn ycps/ycps@psdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as ycps

SQL> begin
2 dbms_job.run（84）;
3 end;
4 /

PL/SQL procedure successfully completed
看来Authid Current_User的使用是有局限的，在通过存储过程来执行DDL语句时需要注意授权问题。调整表的CHAR/VARCHAR2字段的Byte为CHARexpdp遭遇ORA-39006、ORA-39065、ORA-01403、ORA-39097错误相关资讯 Oracle基础

Oracle基础介绍及常用相关SQL*PLUS （03月11日）
Oracle 角色的两个特性和误区（09/04/2012 05:56:16）
Oracle rac11.2.0.3.0的vip在重启（09/02/2012 10:00:39）

Oracle中删除用户遇到的问题（09/08/2012 20:01:42）
IMP-00008: unrecognized （09/02/2012 10:03:25）
在Oracle数据库上设置限制ip地址访（09/02/2012 09:59:55）

本文评论查看全部评论（0）