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>
问题解决!

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12129601/viewspace-762375/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12129601/viewspace-762375/