我在DBA权限的用户下 执行以下操作,可以执行.但是将这些语句写成procedure后,不能执行,是什么原因。还有在procedure里cursor用dba_tables表,编译报错。table or view does not exist。
declare
cursor cur1 is select owner,table_name from all_tables where owner in ('TEST1','TEST2');
v_sql varchar2(500);
v_table_name varchar2(100);
v_user varchar2(100);
begin
for a in cur1 loop
v_user:=a.owner;
v_table_name:=a.table_name;
v_sql :='analyze table '||v_user||'.'||v_table_name||' estimate statistics sample 20 percent';
execute immediate v_sql;
end loop;
end;
/
declare
cursor cur1 is select owner,table_name from all_tables where owner in ('TEST1','TEST2');
v_sql varchar2(500);
v_table_name varchar2(100);
v_user varchar2(100);
begin
for a in cur1 loop
v_user:=a.owner;
v_table_name:=a.table_name;
v_sql :='analyze table '||v_user||'.'||v_table_name||' estimate statistics sample 20 percent';
execute immediate v_sql;
end loop;
end;
/
你需要授予你的用户 select,update,delete权限操作目标用户