Oracle | DBMS_AUTO_TASK_ADMIN Demos, Syntax, and Example Code

Oracle DBMS_AUTO_TASK_ADMIN Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Note: Optimizer statistics are automatically gathered by automatic optimizer statistics collection, which gathers statistics on all objects in the database which have stale or missing statistics. Automatic optimizer statistics collection runs as part of the automated maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined maintenance windows.

AUTHID DEFINER

Constants

Name	Data Type	Value
Attribute Value Flags
ATTRVAL_FALSE	VARCHAR2(5)	'FALSE';
ATTRVAL_TRUE	VARCHAR2(5)	'TRUE'
Mutually Exclusive Doublet
HEAVYWEIGHT	VARCHAR2(16)	'HEAVYWEIGHT'
LIGHTWEIGHT	VARCHAR2(16)	'LIGHTWEIGHT'
Mutually Exclusive Doublet
STABLE	VARCHAR2(16)	'STABLE'
VOLATILE	VARCHAR2(16)	'VOLATILE'
Mutually Exclusive Doublet
DO_NOT_KILL	VARCHAR2(16)	'DO_NOT_KILL'
SAFE_TO_KILL	VARCHAR2(16)	'SAFE_TO_KILL'
Option Flags
OPTFLG_DEFERRED	VARCHAR2(16)	'DEFERRED'
OPTFLG_IMMEDIATE	VARCHAR2(16)	'IMMEDIATE'
Task Priorities
PRIORITY_CLEAR	VARCHAR2(6)	'CLEAR'
PRIORITY_HIGH	VARCHAR2(6)	'HIGH'
PRIORITY_MEDIUM	VARCHAR2(6)	'MEDIUM'
PRIORITY_URGENT	VARCHAR2(6)	'URGENT'

Dependencies

DBMS_ADBTASK_ADMIN	DBMS_MANAGEMENT_PACKS	DBMS_SWAT
DBMS_AUTOIM_INTERNAL	DBMS_SPM	DBMS_SWAT_ARM_INTERNAL
DBMS_AUTOTASK_PRVT_LIB	DBMS_SPM_INTERNAL	DBMS_SWAT_VER_INTERNAL
DBMS_AUTO_INDEX_INTERNAL	DBMS_STANDARD	KET$_AUTOTASK_STATUS
DBMS_AUTOTASK_EXPORT	DBMS_STATS	KET$_CLIENT_CONFIG
DBMS_AUTO_ZONEMAP_INTERNAL	DBMS_STATS_INTERNAL	X$KETCL
DBMS_ILM_ADMIN

Documented Yes: Packages and Types Reference

First Available 11.1

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to the DBA, DATAPUMP_IMP_FULL_DATABASE, and IMP_FULL_DATABASE roles

Source {ORACLE_HOME}/rdbms/admin/dbmsatsk.sql

Subprograms

DEFAULT_RESET	GET_CLIENT_ATTRIBUTES	SET_ATTRIBUTE
DISABLE	GET_P1_RESOURCES	SET_CLIENT_SERVICE
DISABLE_TRACING	MODIFY_AUTOTASK_SETTINGS	SET_P1_RESOURCES
ENABLE	OVERRIDE_PRIORITY	SKIP_SUSPENSION
ENABLE_TRACING	RUN_TASK_MANUAL

DEFAULT_RESET

Resets AUTOTASK configuration setting to factory defaults dbms_auto_task_admin.default_reset( client_name IN VARCHAR2, -- client name or ALL operation_name IN VARCHAR2 DEFAULT ALL); -- operation name or ALL

exec dbms_auto_task_admin.default_reset('ALL', 'ALL');

      

      PL/SQL procedure successfully completed.

DISABLE

Immediately disables all tasks
Overload 1 dbms_auto_task_admin.disable;

exec dbms_auto_task_admin.disable;

      

      PL/SQL procedure successfully completed.

Prevents AUTOTASK from executing any requests from a specified client or operation

Overload 2 dbms_auto_task_admin.disable( client_name IN VARCHAR2, operation IN VARCHAR2, window_name IN VARCHAR2);

-- disable collection of optimizer statistics

      desc dba_autotask_client

      

      col client_name format a35

      col mean_job_duration format a30

      

      SELECT client_name, status, mean_job_duration

      FROM dba_autotask_client;

      

      BEGIN

        dbms_auto_task_admin.disable('auto optimizer stats collection', NULL, NULL);

      END;

      /

      

      PL/SQL procedure successfully completed.

      
      

      SELECT client_name, status, mean_job_duration

      FROM dba_autotask_client;

DISABLE_TRACING (new 23ai)

Disable Autotask layer tracing dbms_auto_task_admin.disable_tracing; PRAGMA SUPPLEMENTAL_LOG_DATA(DISABLE_TRACING, READ_ONLY);

exec dbms_auto_task_admin.disable_tracing;

      

      PL/SQL procedure successfully completed.

ENABLE

Immediately enables all previously disabled tasks
Overload 1 dbms_auto_task_admin.enable;

exec dbms_auto_task_admin.enable;

      

      PL/SQL procedure successfully completed.

Allows a previously disabled client, operation, target type, or individual target to be enabled under AUTOTASK control.

Overload 2 dbms_auto_task_admin.enable( client_name IN VARCHAR2, operation IN VARCHAR2, window_name IN VARCHAR2);

-- reenable collection of optimizer statistics

      desc dba_autotask_client

      

      col client_name format a35

      col mean_job_duration format a30

      

      SELECT client_name, status, mean_job_duration

      FROM dba_autotask_client;

      

      BEGIN

        dbms_auto_task_admin.enable('auto optimizer stats collection', NULL, NULL);

      END;

      /

      

      PL/SQL procedure successfully completed.

      
      

      SELECT client_name, status, mean_job_duration

      FROM dba_autotask_client;

ENABLE_TRACING (new 23ai)

Enable Autotask layer tracing dbms_auto_task_admin.enable_tracing; PRAGMA SUPPLEMENTAL_LOG_DATA(ENABLE_TRACING, READ_ONLY);

exec dbms_auto_task_admin.enable_tracing;

      

      PL/SQL procedure successfully completed.

GET_CLIENT_ATTRIBUTES

Returns values of select client attributes dbms_auto_task_admin.get_client_attributes( client_name IN VARCHAR2, -- name from DBA_AUTOTASK_CLIENT service_name OUT VARCHAR2, -- Service name for client, may be NULL window_group OUT VARCHAR2); -- Name of the active window group

desc dba_autotask_client

      

      set linesize 121

      col client_name format a35

      col consumer_group format a25

      col service_name format a15

      col window_group format a20

      

      SELECT client_name, status, consumer_group, window_group

      FROM dba_autotask_client;

      

      set serveroutput on

      

      DECLARE

       sname dba_autotask_client.service_name%TYPE;

       wgrp  dba_autotask_client.window_group%TYPE;

      BEGIN

        dbms_auto_task_admin.get_client_attributes('sql tuning advisor', sname, wgrp);

      

        dbms_output.put_line('Service: ' || sname);

        dbms_output.put_line('Window:  ' || wgrp);

      END;

      /

GET_P1_RESOURCES

Returns percent of resources allocated to each AUTOTASK High Priority Consumer Group: Total adds to 100% dbms_auto_task_admin.get_p1_resources( stats_group_pct OUT NUMBER, -- %resources for Statistics Gathering seg_group_pct OUT NUMBER, -- %resources for Space Management tune_group_pct OUT NUMBER, -- %resources for SQL Tuning health_group_pct OUT NUMBER); -- %resources for Health Checks

Deprecated as of 12cR1

MODIFY_AUTOTASK_SETTINGS

Use to modify AUTOTASK attributes dbms_auto_task_admin.modify_autotask_settings( client_name IN VARCHAR2, -- Name of the client as found in DBA_AUTOTASK_CLIENT View. attribute_name IN VARCHAR2, -- Attribute to be set attribute_value IN NUMBER -- Attribute value in numeric form

exec dbms_auto_task_admin.modify_autotask_settings('SQL_TUNING_ADVISOR', 'INTERVAL', 12);

      

      PL/SQL procedure successfully completed.

      
      

      exec dbms_auto_task_admin.modify_autotask_settings('AUTO_SPACE_ADVISOR', 'MAX_RUN_TIME', 60);

      

      PL/SQL procedure successfully completed.

OVERRIDE_PRIORITY

Manually override task priority

Overload 1 dbms_auto_task_admin.override_priority( client_name IN VARCHAR2, priority IN VARCHAR2);

Deprecated as of 12cR1

      

      SELECT client_name, priority_override

      FROM dba_autotask_client;

      

      exec dbms_auto_task_admin.override_priority('sql tuning advisor', dbms_auto_task_admin.PRIORITY_HIGH);

      

      SELECT client_name, priority_override

      FROM dba_autotask_client;

Overload 2 dbms_auto_task_admin.override_priority( client_name IN VARCHAR2, operation IN VARCHAR2, -- as shown in DBA_AUTOTASK_OPERATION priority IN VARCHAR2);

Deprecated as of 12cR1

      

      desc dba_autotask_operation

      

      set linesize 121

      col client_name format a35

      col operation_name format a30

      

      SELECT client_name, operation_name, priority_override

      FROM dba_autotask_operation;

      

      exec dbms_auto_task_admin.override_priority('sql tuning advisor', 'automatic sql tuning task', dbms_auto_task_admin.PRIORITY_MEDIUM);

      

      PL/SQL procedure successfully completed.

      

      SELECT client_name, operation_name, priority_override

      FROM dba_autotask_operation;

RUN_TASK_MANUAL (new 23ai)

Runs the specified task in the current session. If an ongoing job is running for the same task in the background, this API will fail dbms_auto_task_admin.run_task-manual(task_name IN VARCHAR2);

TBD

SET_ATTRIBUTE

Set Boolean attributes for a client, operation, or task

Overload 1 dbms_auto_task_admin.set_attribute( client_name IN VARCHAR2, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2);

desc dba_autotask_client

      

      set linesize 121

      col attributes format a60

      

      SELECT client_name, attributes

      FROM dba_autotask_client;

      

      exec dbms_auto_task_admin.set_attribute('sql tuning advisor', 'DO_NOT_KILL', 'TRUE');

      

      PL/SQL procedure successfully completed.

      
      

      SELECT client_name, attributes

      FROM dba_autotask_client;

      

      exec dbms_auto_task_admin.set_attribute('sql tuning advisor', 'SAFE_TO_KILL', 'TRUE');

      

      PL/SQL procedure successfully completed.

      
      

      SELECT client_name, attributes

      FROM dba_autotask_client;

Overload 2 dbms_auto_task_admin.set_attribute( client_name IN VARCHAR2, operation IN VARCHAR2, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2);

desc dba_autotask_operation

      

      set linesize 131

      col client_name format a35

      col attributes format a60

      

      SELECT client_name, operation_name, attributes

      FROM dba_autotask_operation;

      

      exec dbms_auto_task_admin.set_attribute('sql tuning advisor', 'automatic sql tuning task', 'SAFE_TO_KILL', 'TRUE');

      

      PL/SQL procedure successfully completed.

      

      SELECT client_name, operation_name, attributes

      FROM dba_autotask_operation;

SET_CLIENT_SERVICE

Associates an AUTOTASK Client with a specified service dbms_auto_task_admin.set_client_service( client_name IN VARCHAR2, -- from DBA_AUTOTASK_CLIENT service_name IN VARCHAR2); -- Service name for client, may be NULL

SELECT client_name, service_name

      FROM dba_autotask_client;

      

      SELECT name

      FROM dba_services;

      

      DECLARE

       sname dba_autotask_client.service_name%TYPE;

      BEGIN

        dbms_auto_task_admin.set_client_service('sql tuning advisor', 'orabase');

      END;

      /

      

      PL/SQL procedure successfully completed.

      

      SELECT client_name, service_name

      FROM dba_autotask_client;

SET_P1_RESOURCES

Sets percentage-based resource allocation for each High Priority Consumer Group used by AUTOTASK Clients: Must total 100% dbms_auto_task_admin.set_p1_resources( stats_group_pct IN NUMBER, -- %resources for Statistics Gathering seg_group_pct IN NUMBER, -- %resources for Space Management tune_group_pct IN NUMBER, -- %resources for SQL Tuning health_group_pct IN NUMBER); -- %resources for Health Checks

Deprecated as of 12cR1

      

      desc dba_rsrc_consumer_groups

      

      SELECT consumer_group_id, consumer_group

      FROM dba_rsrc_consumer_groups;

      

      desc resource_plan_directive$

      

      SELECT plan, group_or_subplan, mgmt_p1

      FROM resource_plan_directive$

      WHERE (group_or_subplan LIKE '%STATS%'

         OR  group_or_subplan LIKE '%SPACE%'

         OR  group_or_subplan LIKE '%SQL%'

         OR  group_or_subplan LIKE '%HEALTH%');

      

      exec dbms_auto_task_admin.set_p1_resources(10,20,30,40);

      

      PL/SQL procedure successfully completed.

      

      SELECT plan, group_or_subplan, mgmt_p1

      FROM resource_plan_directive$

      WHERE (group_or_subplan LIKE '%STATS%'

         OR  group_or_subplan LIKE '%SPACE%'

         OR  group_or_subplan LIKE '%SQL%'

         OR  group_or_subplan LIKE '%HEALTH%');

      

      exec dbms_auto_task_admin.set_p1_resources(25,25,25,25);

      

      PL/SQL procedure successfully completed.

      

      SELECT plan, group_or_subplan, mgmt_p1

      FROM resource_plan_directive$

      WHERE (group_or_subplan LIKE '%STATS%'

         OR  group_or_subplan LIKE '%SPACE%'

         OR  group_or_subplan LIKE '%SQL%'

         OR  group_or_subplan LIKE '%HEALTH%');

RUN_TASK_MANUAL (new 23ai)

Runs the specified task in the current session. If there is an ongoing job running for the same task in the background, this API will fail dbms_auto_task_admin.skip_suspension(task_name IN VARCHAR2);

TBD