run dbms_stats for a set of tables through a PL/SQL block

[hsingh25]

Hi Guys,

I am trying to run dbms_stats for a set of tables through a PL/SQL block and want to incorporate this in a shell script to automate it.

I am using execuute immediate, but I am getting the following error when I run this block:

DECLARE
v_stats varchar2(1000);
cursor st_cursor is
select owner, table_name from dba_tables where owner='XYZ' and table_name in(
'XX,
YY,
'ZZ',
'ZZ1',
'ZZ2');
BEGIN
for st_row in st_cursor loop
v_stats := 'execute dbms_stats.gather_table_stats(ownname=>'||''''||st _row.owner||''''||','||'tabname=>'||''''||st_row.t a
ble_name||''''||','||'estimate_percent=>1'||','||' method_opt=>'||''''||'FOR ALL INDEXED COLUMNS SIZE SKEWONLY'||''''||','||'
degree=> 6'||','||'granularity=>'||''''||'GLOBAL'||''''||', '||'cascade=>false);';
execute immediate v_stats;
end loop;
END;
/

ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 13

I will be running gather_index_stats in a separate cursor within this block, but want this to work. Any help would be appreciated

[MMcCarthy]

I moved this question to it's own thread. You are more likely to receive assistance this way.

ADMIN

[Saii]

when calling a proc/function in PL/SQL block, we dont need to use "execute proc", directly call it.

[chandu031]

Hi Guys,

I am trying to run dbms_stats for a set of tables through a PL/SQL block and want to incorporate this in a shell script to automate it.

I am using execuute immediate, but I am getting the following error when I run this block:

DECLARE
v_stats varchar2(1000);
cursor st_cursor is
select owner, table_name from dba_tables where owner='XYZ' and table_name in(
'XX',
'YY',
'ZZ',
'ZZ1',
'ZZ2');
BEGIN
for st_row in st_cursor loop
v_stats := 'execute dbms_stats.gather_table_stats(ownname=>'||''''||st _row.owner||''''||','||'tabname=>'||''''||st_row.t a
ble_name||''''||','||'estimate_percent=>1'||','||' method_opt=>'||''''||'FOR ALL INDEXED COLUMNS SIZE SKEWONLY'||''''||','||'
degree=> 6'||','||'granularity=>'||''''||'GLOBAL'||''''||', '||'cascade=>false);';
execute immediate v_stats;
end loop;
END;
/

ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 13

I will be running gather_index_stats in a separate cursor within this block, but want this to work. Any help would be appreciated

Hi ,
Try this out:

 
DECLARE 
v_stats varchar2(1000); 
 
BEGIN 
 
for st_row in (select owner, table_name from all_tables where owner='XYZ and table_name in( 
'XX, 
  YY, 
  'ZZ', 
  'ZZ1', 
  'ZZ2')
)
loop
 
v_stats := 'begin dbms_stats.gather_table_stats(ownname=>'||''''||st_row.owner||''''||','||'tabname=>'||''''||
st_row.table_name||''''||','||'estimate_percent=>1'||','||'method_opt=>'||''''||'FOR ALL INDEXED COLUMNS SIZE SKEWONLY'||''''||','||' 
degree=> 6'||','||'granularity=>'||''''||'GLOBAL'||''''||','||'cascade=>false); end;'; 
execute immediate v_stats; 
end loop; 
 
end; 
 

Hope this helps!