NOTE:858852.1 - DBA_AUTOTASK_TASK and DBA_AUTOTASK

In this Document
Symptoms
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

We are Trying to Disable the Auto Optimizer Stats Collection in 11G.
DBA_AUTOTASK_CLIENT shows that the 'auto optimizer stats collection' is disabled. But DBA_AUTOTASK_TASK shows that the Task is Enabled.

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

PL/SQL procedure successfully completed.

Checking the Status from DBA_AUTOTASK_TASK & DBA_AUTOTASK_CLIENT

SQL> select client_name,status from DBA_AUTOTASK_TASK;
.
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED <<===
auto space advisor ENABLED
sql tuning advisor ENABLED
.
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

Cause

This is expected Behaviour.
The views assume that there is a one to many relationship between CLIENTS and TASKS.
Task can be used by different/multiple client. So even though if we disable the client, the DBA_AUTOTASK_TASK may still show the status as enabled. In current version, the TASKS has only one CLIENT. But in future version of oracle , the TASKS can have multiple CLIENTS so the status in DBA_AUTOTASK_TASK will show as ENABLED.
So the correct way to check the status is through DBA_AUTOTASK_CLIENT.

Solution

Use DBA_AUTOTASK_CLIENT to check the status.

SQL> select client_name,status from Dba_Autotask_Client;

References

BUG:8605352 - WRONG STATUS INFO IN DBA_AUTOTASK_TASK
NOTE:779596.1 - How Can We Find Out Status Of Task 'Auto Optimizer Stats Collection'