存储过程中动态的创建表空间和表报ORA-01031: insufficient privileges

存储过程中报ORA-01031: insufficient privileges，如下：

declare
col_today  varchar2(20) := concat('rows_', to_char(sysdate, 'mmdd'));
begin
if to_number(to_char(sysdate, 'dd')) = 1 then
    execute immediate 'drop table t purge';
    execute immediate 'create table t as select table_name,owner,num_rows '||col_today||' from dba_tables@todb1 where num_rows>=1000000 order by 3 desc';
end if;
end;
/

PL/SQL procedure successfully completed

 

create or replace procedure p_t is
col_today  varchar2(20) := concat('rows_', to_char(sysdate, 'mmdd'));
begin
if to_number(to_char(sysdate, 'dd')) = 1 then
    execute immediate 'drop table t purge';
    execute immediate 'create table t as select table_name,owner,num_rows '||col_today||' from dba_tables@todb1  where num_rows>=1000000 order by 3 desc';
end if;
end;
/

Procedure created

 

SQL> exec p_t;

ORA-01031: insufficient privileges
ORA-06512: at "USER1.P_T", line 6
ORA-06512: at line 2

 

能在块中执行，在存储过程中却不能执行，这个问题确实很纳闷。

已经授予USER1用户dba角色权限了，还报权限不足，到底是缺少什么权限呢？

通过对过程第6行减少测试范围，发现是create table t时报错。

既然确定了问题所在就好查原因了，网上搜索“存储过程建表”，果然有人跟我遇到了相同的问题，确实是缺少权限，同时给出了解决方案：

grant create any table to user1;

授权后，果然不报错了。

很纳闷的是dba角色怎么会没有create any table权限呢？

另外查得存储过程中只能执行insert、delete、update、select操作，如果要实现其他操作，只能用动态sql去实现。