Oracle通過JOB定時建立序列

因為業務需要每月需要增加一個序列，想到了使用job定時建立，每次建立一年的。寫此job的過程中遇到一些問題，在此記錄一下。

第一步：寫建立序列（sequence）的預存程序。代碼如下：

1. create or replace procedure pro_create_seq(sequencePrefix in varchar2)   
2. is  
3. strYear varchar2(4);  
4. strMonth varchar2(6);  
5. strSql varchar2(500);  
6. begin  
7.   select to_char(sysdate,'yyyy') into strYear from dual;  
8.   for i in 1..12 loop  
9.     strMonth:=strYear||lpad(i,2,'0');--組裝月份，形如：201201   
10.     strSql:='create sequence '||sequencePrefix||strMonth||' start with 1 increment by 1 MINVALUE 0 MAXVALUE 99999999999 ';  
11.     execute immediate strSql;  
12.   end loop;  
13. 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，修改後的預存程序如下：

1. create or replace procedure pro_create_seq(sequencePrefix in varchar2)   
2. Authid Current_User --賦予role許可權   
3. is  
4. strYear varchar2(4);  
5. strMonth varchar2(6);  
6. strSql varchar2(500);  
7. begin  
8.   select to_char(sysdate,'yyyy') into strYear from dual;--取年份，形如：2012   
9.   for i in 1..12 loop  
10.     strMonth:=strYear||lpad(i,2,'0');--組裝月份，形如：201201   
11.     strSql:='create sequence '||sequencePrefix||strMonth||' start with 1 increment by 1 MINVALUE 0 MAXVALUE 99999999999 ';  
12.     execute immediate strSql;  
13.   end loop;  
14. end pro_create_seq;  

SQL> exec pro_create_seq('FACTORY_NOTICE_INFO_DB_');

PL/SQL procedure successfully completed

執行成功。

第二步：建立job，定時調用預存程序來建立sequence。

1. variable job1 number;  
2.  begin  
3.  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)');  
4.  commit;  
5.  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語句時需要注意授權問題。