DBMS_AUTO_TASK_ADMIN.DISABLE still shows the client_name enabled in DBA_AUTOTASK_CLIENT view (Doc ID 1565768.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Goal

The Oracle documentation lists the following related to disabling all autotask jobs:-

"To enable or disable all automated maintenance tasks for all windows, call the ENABLE or DISABLE procedure with no arguments.
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;"

But when executing the above command, it just disables the sql tuning advisor job but not the remaining two jobs: gather stats and segment space.

In some cases also, all of the jobs might be listed as ENABLED:

SQL> select client_name,status from Dba_Autotask_Client;


CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor DISABLED

or


CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED


Why does this happen ?
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms