Here is how you can query if the Auto Optimizer Stats Collection job is running in Oracle Database 11g:
Login to database as SYSDBA:
[sql]
SELECT CLIENT_NAME,
STATUS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = ‘auto optimizer stats collection’;
[/sql]
Below is how the job can be disabled:
[sql htmlscript=”false”]
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => NULL);
[/sql]
Below is how the job can be enabled:
[sql htmlscript=”false”]
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => ‘auto optimizer stats collection’,
operation =>NULL,
window_name => NULL);
[/sql]