Fixing QUESTIONABLE STATISTICS

[jayharper]

I've created a new database from an export of a test database, and noticed (too late) that in the export log of the test database that there are numerous errors: EXP-00091: Exporting questionable statistics.

I would like to clean this up in both databases. I tried using:

exec dbms_stats.gather_schema_stats( -
ownname => 'TEST', -
options => 'GATHER', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 34 -
);

but on my next export of the database, I still have the EXP-00091 error.

What is the correct command to recompute all statistics in a db?

Oracle 9i 9.2.0.4 on Windows Server 2003

THANKS

[hrishy]

Hi

while doing the export just say statistics=none

regards
Hrishy

[dbtoo]

Doc ID: Note:204793.1
Subject: EXP-00091 While Taking Export
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 24-JUL-2002
Last Revision Date: 11-OCT-2002



Fact(s)
~~~~~~~~

Oracle Server - Enterprise Edition 9.0.1
Oracle Server - Enterprise Edition 9.2.0


Symptom(s)
~~~~~~~~~~

EXP-00091: Exporting questionable statistics.


Cause
~~~~~~~
In Oracle 9i by default export attempts to include statistics in the export.
In pre Oracle 9i versions statistics are not included unless it is specified.

Reference:

Bug 1862413 EXPORT & DEFAULT FOR STATISTICS SHOULD BE PRE-9I COMPATIBLE

Note 159787.1 9i: Import STATISTICS=SAFE

Fix
~~~~

a) If the statistics of a table are not required to include in export
take the export with parameter STATISTICS=NONE


Example:

$exp scott/tiger file=emp1.dmp tables=emp STATISTICS=NONE

b) In case, the statistics are need to be included can use STATISTICS=ESTIMATE or
COMPUTE (default is Estimate).