auto analyze partition !!
the following SP is not working tho it compiles fine.
Looks like the "for x in select " is not working....any suggestion ??
CREATE OR REPLACE PROCEDURE analyze_partition(
part_name IN VARCHAR2)
IS
TYPE x_type IS REF CURSOR;
x x_type;
x_part_name varchar2(200) := upper(part_name);
BEGIN
dbms_output.put_line('X_PART_NAME: '|| x_part_name);
for x in (select table_name,partition_name from dba_tab_partitions
where table_owner = 'MDM3'
and partition_name like 'x_part_name%'
and last_analyzed is null)
loop
begin
exec sys.dbms_stats.gather_table_stats(ownname => 'MDM3', -
tabname => x.table_name, -
partname => x.partition_name, -
estimate_percent => 30, -
block_sample => false,-
method_opt => 'FOR ALL COLUMNS SIZE 1', -
degree => 8, -
granularity => 'default', -
cascade => true , -
stattab => null, -
statid => null, -
statown => null);
exception
when others then
dbms_output.put_line('### ERROR: ' || sqlerrm);
end;
end loop;
end;