Re: In search of script to generate analyze command

If you are using 9i try this. Not sure how much of the following is in 8i.

Using the dbms_stats package.

ALTER_SCHEMA_TABLE_MONITORING Procedure
This procedure enable or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Using this procedure is equivalent to issuing ALTER TABLE...MONITORING (or NOMONITORING) individually. You should enable monitoring if you use GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS with the GATHER AUTO or GATHER STALE options.

GATHER_SCHEMA_STATS Procedure
This procedure gathers statistics for all objects in a schema.

And then maybe something like this.

declare
 l_objList dbms_stats.objectTab;
begin
 dbms_stats.gather_schema_stats

 ( ownname        => 'PROD',
   options        => 'LIST STALE',
   objlist        => l_objList );

 for i in 1 .. l_objList.count
 loop
   dbms_output.put_line( l_objList(i).objType );    dbms_output.put_line( l_objList(i).objName );  end loop;
end;
/

"Patrick Burns" <pburns13_at_hotmail.com> wrote in message news:61a26a0d.0410201203.c196837_at_posting.google.com...
>I am in search of a generic script that will run everyday in our
> nightly cycle and write to an sql file an analyze statement if a
> certain percentage of the rows have changed, 20%. Then every weekend
> use dbms_stats to analyze the tables and indexes.
>
> I have seen these types of things in this group but can't seem to find
> one around.
Received on Wed Oct 20 2004 - 23:07:45 CDT