执行动态SQL报ORA-00942的解决办法

环境:

OS:Red Hat Linux As5

DB:10.2.0.4

 

今天写了个自动维护分区的程序,mydb用户具有dba权限,自动分区维护在mydba用户下,scott创建了分区表,然后然后执行自动分区程序,自动维护分区程序执行的是动态DDL,执行的时候报ORA-00942: 表或视图不存在,将该执行语句提取出来执行没问题.

 

1.查看mydba的权限
Select Pri.Grantee,
       Pri.Privilege,
       Pri.Admin_Option,
       Case
         When Type = '1' Then
          'Privs'
         When Type = '2' Then
          'Role'
       End Type
  From (Select Sp.Grantee, Sp.Privilege, Sp.Admin_Option, '1' Type
          From Dba_Sys_Privs Sp
        Union All
        Select Rp.Grantee, Rp.Granted_Role, Rp.Admin_Option, '2' Type
          From Dba_Role_Privs Rp) Pri
 Where Pri.Grantee = 'MYDBA'

GRANTEE PRIVILEGE ADMIN_OPTION TYPE
------------------------------------
MYDBA UNLIMITED TABLESPACE NO Privs
MYDBA DBA                   NO Role

 

2.执行自动分区程序

SQL> Declare
  2    po_error_msg Varchar2(4000);
  3  begin
  4    pkg_management_part_table.management_part_table(po_error_msg);
  5    dbms_output.put_line(po_error_msg);
  6  end;
  7  /
alter table SCOTT.TB_RANGE_PART_TEST02 add partition P_20120702
values less than(to_date(20120703,'YYYYMMDD'))
ORA-00942: table or view does not exist

 

直接执行SQL
SQL> alter table SCOTT.TB_RANGE_PART_TEST02 add partition P_20120702
  2  values less than(to_date(20120703,'YYYYMMDD'));

Table altered.

 

没有问题.

 

解决这个问题有两种办法,一种是将表的权限赋予mydba,另一种是在创建过程和包头上加上AUTHID CURRENT_USER.第一种方法对于维护的表很多的情况下,逐一赋予权限比较麻烦,这里采用第二种方法.
Create Or Replace Package Pkg_Management_Part_Table  AUTHID CURRENT_USER  Is
  Function Get_Max_Part_Name(Pi_Owner Varchar2, Pi_Table_Name Varchar2)
    Return Varchar2;
  Procedure Management_Part_Table(Po_Error_Msg Out Varchar2);
End;

 

3.再次执行过程
SQL> Declare
  2    po_error_msg Varchar2(4000);
  3  begin
  4    pkg_management_part_table.management_part_table(po_error_msg);
  5    dbms_output.put_line(po_error_msg);
  6  end;
  7  /

alter table SCOTT.TB_RANGE_PART_TEST02 add partition P_20120703
values less than(to_date(20120704,'YYYYMMDD'))

 

PL/SQL procedure successfully completed.

 

Oracle文档是这样说的:
You need AUTHID CURRENT_USER to execute dynamic package methods; otherwise, these methods raise an insufficient privileges error.

 

-- The End --