Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to create dbms_stast.gather_table_stats script

Re: how to create dbms_stast.gather_table_stats script

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 23 Aug 2002 18:41:38 +1000
Message-ID: <ajm99.12678$g9.41460@newsfeeds.bigpond.com>


Hi Max,

OK, tea's just about up so here's a bit more of a hint:

If you just want to run dbms_stats for all objects in a schema, then straight from the manual look at:

DBMS_STATS.GATHER_SCHEMA_STATS (
   ownname VARCHAR2,
   estimate_percent NUMBER DEFAULT NULL,

   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
   degree           NUMBER   DEFAULT NULL,
   granularity      VARCHAR2 DEFAULT 'DEFAULT',
   cascade          BOOLEAN  DEFAULT FALSE);

If you want to write some dynamic script, you need this kinda logic:

...
for each record in the cursor

    fetch the next user_table details

    execute dbms_stats.gather_table_stats(ownname=>'whoever', tabname=> v_table_name, other parameters ....)

end loop;
....

But please, at the very least look at the parameter name list as documented and get it working natively before writing any fancy (or not so fancy) code !!

Dinner Time ;)

Richard

"M.a.x G.e.decke" <spam_at_gedecke.de> wrote in message news:ak4q0r$kuq$1_at_redenix.uni-muenster.de...
> thanx very much, but you weren't very helpful. the problem still exists.
i'm
> already trying to get this to work for some time now and i'm hangin at the
> moment...
>
>
> max
>
>
Received on Fri Aug 23 2002 - 03:41:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US