我在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;
/
我是想在DBA用户下,对其他的用户的表都分析一下,写成一个procedure.
直接执行上面的pl/sql ,能对TEST1,TEST2用户的表进行分析。不过写到procedure里,就不能分析。如果DBA用户为ORADBA
create or replace procedure p_analyze_tab
is
cursor cur1 is select owner,table_name from all_tables where owner='ORADBA';
end;
/
存储过程会将ORADBA用户下的表分析
但是 cursor cur1 is select owner,table_name from all_tables where owner in ('TEST1','TEST2');
存储过程编译没问题,但执行的话,就直接结束了,什么都没做。很不解,谁碰到过类似情况呀。
declare可以直接执行,对其他用户的表都能做分析。
写到存储过程里就是不能对表进行分析,好像选不到数据
create or replace procedure p_analyze_tab
is 请哪位高手解答。
authid current_user
is
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;