Remove OLD STATS

[Sambo8]

Hi There,

I think there is a script to remove old stats from the STATS tablespace, e.g older than 1 month. But cannot find it anywhere. Does anyone know of such a script.

Many thanks,

Sam

 

[SantaMufasa]

Sam,

If you want to get rid of statistics for a specific Oracle user/schema, you can issue this command from SQL*Plus:

SQL> exec dbms_utility.ANALYZE_SCHEMA('<schema name>','DELETE')

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

It would probably depend upon how you have gathered your stats

DBMS_UTILITY As per the documentation:

Use this procedure to collect nonoptimizer statistics. For optimizer statistics, use the DBMS_STATS.GATHER_SCHEMA_STATS procedure.

I would suggest possibly using
DBMS_STATS.DELETE_SCHEMA_STATS.

Check out

http://tahiti.oracle.com

to get documentation for your appropriate version.

 

[SantaMufasa]

Good update, Jim! Thanks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Always a pleasure Dave