11g Auto Scheduler_Jobs Disable

10.2.0.4:
select job_name, job_type, program_name, schedule_name, job_class
from dba_scheduler_jobs
where job_name = 'GATHER_STATS_JOB';

JOB_NAME JOB_TYPE
------------------------------ ----------------
PROGRAM_NAME
--------------------------------------------------------------------------------
SCHEDULE_NAME
--------------------------------------------------------------------------------
JOB_CLASS
------------------------------
GATHER_STATS_JOB
GATHER_STATS_PROG
MAINTENANCE_WINDOW_GROUP
AUTO_TASKS_JOB_CLASS


SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 16 16:08:18 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SYS@CGV > select job_name, job_type, program_name, schedule_name, job_class
2 from dba_scheduler_jobs
3 where job_name = 'GATHER_STATS_JOB';

no rows selected

SQL >

0. 모든 자동작업 DISABLE
execute DBMS_AUTO_TASK_ADMIN.DISABLE;

1. DISABLE 시키는 방법
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
Tjhis can be verified with:

BEGIN
DBMS_SCHEDULER.DISABLE('PURGE_LOG');
END;
/

2. 상태 확인
SQL> select client_name,status from Dba_Autotask_Client;
CLIENT_NAME STATUS
---------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
to re-enable again:

SQL> SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='PURGE_LOG';

JOB_NAME STATE
------------------------------ ---------------
PURGE_LOG DISABLED

3. ENABLE 시키는 방법
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/


간단정리

On 10g, connect as SYS and EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
On 11g, connect as SYS and EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('auto optimizer stats collection', NULL, NULL);