Re: Re: pl/sql simple help - analyze table avail?

surman_at_oracle.com writes:
>In article <odysscci.258.000E0507_at_teleport.com>, odysscci_at_teleport.com (Jim Kennedy) writes:
 [stuff deleted]
>|> create or replace procedure analyze_all is
>|> cursor c1 is
>|> select table_name,owner from all_tables where owner not in('SYS','SYSTEM');
>|> list_to c1%ROWTYPE;
>|> tabName varchar2(255);
>|>
>|> BEGIN
>|> open c1;
>|> loop
>|> fetch c1 into list_to;
>|> exit when c1%NOTFOUND;
>|> tabName:= list_to.owner ||'.' ||list_to.table_name ;
>|>
>|> -- won't work -> analyze table :tabName compute statistics;
>|>
>|> commit;
>|> end loop;
>|> close c1;
>|> END analyze_all;
>|>
>|> I have tried execute immediate and building a string for that.
>|>
>|> Anyone have some suggestions? I would prefer that this was a stored
>|> procedure that some process can kick off every so often.
>|>
>|> TIA,
>|> Jim Kennedy
>|>
>
>This is dynamic SQL, for which you need the DBMS_SQL package which is shipped
>with Oracle 7.1. You can't use a variable for the name of an Oracle object,
>such as a table or column name.
>
You can use DBMS_UTILITY.ANALYZE_SCHEMA packaged procedure which is avaliable in Oracle 7.0 with the procedural option.