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.gath er_table_stats( ownname=>'||''' '||st_row.owner ||''''||','||'t abname=>'||'''' ||st_row.ta
ble_name||''''| |','||'estimate _percent=>1'||' ,'||'method_opt =>'||''''||'FO R ALL INDEXED COLUMNS SIZE SKEWONLY'||'''' ||','||'
degree=> 6'||','||'granu larity=>'||'''' ||'GLOBAL'||''' '||','||'cascad e=>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_st ats 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.gath er_table_stats( ownname=>'||''' '||st_row.owner ||''''||','||'t abname=>'||'''' ||st_row.ta
ble_name||''''| |','||'estimate _percent=>1'||' ,'||'method_opt =>'||''''||'FO R ALL INDEXED COLUMNS SIZE SKEWONLY'||'''' ||','||'
degree=> 6'||','||'granu larity=>'||'''' ||'GLOBAL'||''' '||','||'cascad e=>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_st ats in a separate cursor within this block, but want this to work. Any help would be appreciated

Hi ,
Try this out:

[code=sql]

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.gath er_table_stats( ownname=>'||''' '||st_row.owner ||''''||','||'t abname=>'||'''' ||
st_row.table_na me||''''||','|| 'estimate_perce nt=>1'||','||'m ethod_opt=>'||' '''||'FOR ALL INDEXED COLUMNS SIZE SKEWONLY'||'''' ||','||'
degree=> 6'||','||'granu larity=>'||'''' ||'GLOBAL'||''' '||','||'cascad e=>false); end;';
execute immediate v_stats;
end loop;

end;
[/code]

Hope this helps!