Analyze table

v_percent constant number := 40;
type t_obj_names is table of user_objects.object_name%type;
v_tables t_obj_names := t_obj_names();

cursor c_t is
select /*+ALL_ROWS*/
table_name
from user_tables
where tablespace_name = 'AA'
and substr(table_name, 1, 1) not in ('S');

begin

n := 0;
for r in c_t loop
v_tables.extend;
n := n + 1;
v_tables(n) := r.table_name;
end loop;
for i in 1 .. n loop
begin
dbms_stats.gather_table_stats(user,
v_tables(i),
estimate_percent => v_percent);
commit;
exception
when others then
rollback;
commit;
end;
end loop;

end;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10458681/viewspace-928612/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10458681/viewspace-928612/