[Database] How to Disable Automatic Statistics Collection in 10G ?

The information in this document applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.1 to 10.2.0.0
Information in this document applies to any platform.

Goal

How to Disable Automatic Statistics Collection in 10G ?

Note
The Automatic Statistics collection feature is enabled by default in 10G.
You can verify this by checking the following :
SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
STATE
---------------
SCHEDULED

Solution

1)To Disable the automatic statistics collection in 10G , you can execute the following procedure :
SQL> EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB')';

2) To check whether the job is disabled, run the following QUERY:
SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
STATE
---------------
DISABLED


Metalink Note: 311836.1