ORA-01031,ORA-01031錯的分析與處理

最近有個同事想寫個儲存過程來實現定期重建某個分割槽表的分割槽索引。

儲存過程如下：

CREATE OR REPLACE PROCEDURE sp_gather_stats_tabs AS

BEGIN

    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121231 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121230 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121229 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121228 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121227 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121226 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121225 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121224 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121223 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121222 online';
    execute immediate 'alter index UI_RPT_IMPEXP_DETAIL_1  rebuild partition P20121221 online';

    BEGIN
    dbms_stats.gather_index_stats(ownname => 'APP',indname => 'UI_RPT_IMPEXP_DETAIL_1',estimate_percent => 10);
    END;

    BEGIN
    dbms_stats.gather_table_stats(ownname => 'APP',tabname => 'RPT_IMPEXP_DETAIL',estimate_percent => 5);
    END;

END;

 

建立完畢，編譯通過，可是反映說呼叫執行是報許可權錯誤：

SQL> execute sp_gather_stats_tabs;
 
begin sp_gather_stats_tabs; end;
 
ORA-01031: 許可權不足
ORA-06512: 在 "APP.SP_GATHER_STATS_TABS", line 5
ORA-06512: 在 line 2
 
根據這個錯誤說明:在儲存過程中使用的到系統許可權不能通過rule來授權,需要顯示授權,或者使用AUTHID CURRENT_USER，在儲存過程中新增

AUTHID CURRENT_USER,即：

CREATE OR REPLACE PROCEDURE sp_gather_stats_tabs AUTHID CURRENT_USER AS

。。。。

再次呼叫：

SQL> execute sp_gather_stats_tabs;

SQL> /
 
PL/SQL procedure successfully completed

 

開始建立job，配置沒半個月自動執行：

begin
  sys.dbms_job.submit(job => :job,
                      what => 'sp_gather_stats_tabs;',
                      next_date => to_date('01-06-2013 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+15');
  commit;
end;
/

job建立完畢。

可是發現後臺alert日誌還是報如下錯誤：

Errors in file /home/oracle/admin/cnpl/udump/cnpl_ora_16054.trc:
ORA-01031: 許可權不足
Wed May 29 14:23:40 2013
Errors in file /home/oracle/admin/cnpl/udump/cnpl_ora_16054.trc:
ORA-12012: 自動執行作業 362 出錯
ORA-01031: 許可權不足
ORA-06512: 在 "APP.SP_GATHER_STATS_TABS", line 5
ORA-06512: 在 line 1
對應的app使用者已經具備resource角色了。

看來得做一個10046的trace檔案，看看中間究竟缺少什麼許可權：

SQL>
SQL> set lines 160
SQL> set pages 1000
SQL>
SQL>
SQL> SELECT  d.VALUE|| '/'                                                                                                                                    
         || LOWER (RTRIM (i.INSTANCE, CHR (0)))                                                                                           
         || '_ora_'                                                                                                                         
         || p.spid                                                           
         || '.trc'                                                           
            AS "trace_file_name"                                             
  FROM   (SELECT   p.spid                                                    
            FROM   v$mystat m, v$session s, v$process p                      
           WHERE   m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
         (SELECT   t.INSTANCE                                                
            FROM   v$thread t, v$parameter v                                 
           WHERE   v.NAME = 'thread'                                         
                   AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,  
         (SELECT   VALUE                                                     
            FROM   v$parameter                                               
           WHERE   NAME = 'user_dump_dest') d;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16 

trace_file_name
----------------------------------------------------------------------------------------------------------------------------------------------------------------
/home/oracle/admin/cnpl/udump/cnpl_ora_16054.trc

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> alter session set events '1031 trace name errorstack level 1';

Session altered.

SQL> DECLARE
BEGIN
dbms_job.run(362);
END;
/  2    3    4    5 
DECLARE
*
ERROR at line 1:
ORA-12011: 無法執行 1 作業
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 275
ORA-06512: at line 3

SQL> alter session set events '1031 trace name errorstack off';

Session altered.

SQL> alter session set events '10046 trace name context off'; 

Session altered.

SQL> ！

檢視trace檔案：/home/oracle/admin/cnpl/udump/cnpl_ora_16054.trc
vi ：/home/oracle/admin/cnpl/udump/cnpl_ora_16054.trc

BINDS #14:
EXEC #14:c=1000,e=231,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=1,tim=1337703729593208
FETCH #14:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=1,tim=1337703729593230
STAT #14 id=1 cnt=0 pid=0 pos=1 bj=0 p='FILTER  (cr=0 pr=0 pw=0 time=202 us)'
STAT #14 id=2 cnt=0 pid=1 pos=1 bj=258 p='TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us)'
*** 2013-05-29 14:23:39.103
ksedmp: internal or fatal error
ORA-01031: 許可權不足
Current SQL statement for this session:
create table "APP"."SYS_JOURNAL_297412" (C0 VARCHAR2(20),  opcode char(1), partno number,  rid rowid, primary key( C0 , rid )) organization index TABLESPACE "APPDATA"
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xf0888e80         5  procedure APP.SP_GATHER_STATS_TABS
0x127905208         1  anonymous block
0x11fc041c0       406  package body SYS.DBMS_IJOB
0x127e10128       275  package body SYS.DBMS_JOB
0xf32dd940         3  anonymous block
----- Call Stack Trace -----
看來是需要建立一張表SYS_JOURNAL_297412,需要對app使用者做顯示的建表許可權賦權，而為什麼這個過程中需要建表呢？原始是在儲存過程中有線上rebuild索引的，在rebuild索引的過程中確實需要新建一個臨時表的。此時豁然開朗，通過對app使用者顯示賦權：

SQL> conn /as sysdba
Connected.
SQL> grant create table to app;

Grant succeeded.

SQL> conn app/app
Connected.
SQL> DECLARE
BEGIN
dbms_job.run(362);
END;   2    3    4 
  5  /

PL/SQL procedure successfully completed.

SQL>
問題解決!