关于oracle存储过程中创建动态表的问题！！！

Question

情况描述：我这里有两个数据库分别在两台服务器上，我在本地的数据库上设置了一个定时任务，每半小时从远程数据库上读取表，然后在本地数据库上创建一个相同的表。下面是我写的一个简单的存储过程：CREATE OR REPLACE PROCEDURE test1
as
BEGIN
execute immediate 'DROP TABLE newtest';
execute immediate 'CREATE TABLE newtest AS SELECT aaa as a, bbb as b FROM table1@dblink';
END test1;  就是这么个简单的存储过程，先删除本地的表，然后从远程数据库中读取后动态创建到本地。存储过程本身没有问题，关键就是其中创建表的语句执行有问题，不能创建出表来。希望各位达人指点一二。跪谢了！！！

Answer 1

CREATE TABLE newtest AS SELECT aaa as a, bbb as b FROM table1@dblink
在外面可以执行成功吗?
select on table1@dblink 有没有赋权给用户，通过procedure必须要直接赋权到用户 不能通过role赋权

Answer 2

回楼上的
CREATE TABLE newtest AS SELECT aaa as a, bbb as b FROM table1@dblink 
这句单独拿出来可以成功执行的，只是放到存储过程中就不行了。

Answer 3

你建立的dblink  远程的数据库的用户是否就是表table1所属的用户?

Answer 4

回楼上的：是的。
dblink是没有问题的，现在主要的问题就是在create table上。
受别人启发，是不是应该用loop循环来创建？
可惜我对存储过程不太明白，还不知道怎么弄……

Answer 5

你用工具plsqldev.exe或toad调试一下 看看 是什么错误。
和loop没什么关系的。。

Answer 6

如果表结构相同,建议:CREATE OR REPLACE PROCEDURE test1 
as 
BEGIN 
execute immediate 'Truncate TABLE newtest'; 
execute immediate 'Insert Into newtest SELECT aaa as a, bbb as b FROM table1@dblink'; 
END test1;

Answer 7

终于找到毛病了，原来是没有权限。虽然当前用户执行语句是有权限的，但是放到存储过程中就必须要显式的赋个权限给当前用户。以下是我找到的资料，贴出来给大家也看一下吧。
=====================
【IT168 技术文档】我们知道，用户拥有的role权限在存储过程是不可用的。如：     SQL> select * from dba_role_privs where grantee='SUK';    GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
    ------------ ------------ ------------ ------------
    SUK DBA NO YES
    SUK CONNECT NO YES
    SUK RESOURCE NO YES    --用户SUK拥有DBA这个role    --再创建一个测试存储过程：
    create or replace procedure p_create_table 
    is
    begin
    Execute Immediate 'create table create_table(id int)';
    end p_create_table;    --然后测试
    SQL> exec p_create_table;    begin p_create_table; end;    ORA-01031: 权限不足
    ORA-06512: 在"SUK.P_CREATE_TABLE", line 3
    ORA-06512: 在line 1    --可以看到，即使拥有DBA role，也不能创建表。role在存储过程中不可用。
    --遇到这种情况，我们一般需要显式进行系统权限，如grant create table to suk;
    --但这种方法太麻烦，有时候可能需要进行非常多的授权才能执行存储过程
    --实际上，oracle给我们提供了在存储过程中使用role权限的方法：
    --修改存储过程，加入Authid Current_User时存储过程可以使用role权限。
    create or replace procedure p_create_table 
    Authid Current_User is
    begin
    Execute Immediate 'create table create_table(id int)';
    end p_create_table;    --再尝试执行：
    SQL> exec p_create_table;    PL/SQL procedure successfully completed    --已经可以执行了。
=====================非常感谢xuegongjian的热心帮助！
也非常感谢楼上的dl110，我刚开始就是你这样的想法，不过不知道怎么写，就用了drop然后再create table的笨办法。不过现在你帮我找到更好的答案了，就用你提供的方法了:)
每人一半，迅速结贴。