32 DBMS_AUTO_TASK_ADMIN

The DBMS_AUTO_TASK_ADMIN package provides an interface to AUTOTASK functionality. It is used by the DBA as well as Enterprise Manager to access the AUTOTASK controls. Enterprise Manager also uses the AUTOTASK Advisor.

See Also:

Oracle Database Administrator's Guide for more information about "Configuring Automated Maintenance Task"

This chapter contains the following sections:

Using DBMS_AUTO_TASK_ADMIN

Deprecated Subprograms

Note:

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

Security Model

DBMS_AUTO_TASK_ADMIN is a definer's rights package, and EXECUTE is automatically granted to DBA, IMP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE.

Constants

The DBMS_AUTO_TASK_ADMIN package uses the constants shown in Table 32-1:

Table 32-1 DBMS_AUTO_TASK_ADMIN Constants

Name Type Value Description

PRIORITY_MEDIUM

VARCHAR2

'MEDIUM'

Task with this priority should be executed as time permits

PRIORITY_HIGH

VARCHAR2

'HIGH'

Task with this priority should be executed within the current Maintenance Window

PRIORITY_URGENT

VARCHAR2

'URGENT'

Task with this priority is to be executed at the earliest opportunity

Summary of DBMS_AUTO_TASK_ADMIN Subprograms

Table 32-2 DBMS_XMLSTORE Package Subprograms

Method Description

DISABLE Procedures

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

ENABLE Procedures

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

GET_CLIENT_ATTRIBUTES Procedure

Returns values of select client attributes

GET_P1_RESOURCES Procedure

Returns percent of resources allocated to each AUTOTASK High Priority Consumer Groups

OVERRIDE_PRIORITY Procedures

Manually overrides task priority.

SET_CLIENT_SERVICE Procedure

Associates an AUTOTASK Client with a specified Service

SET_P1_RESOURCES Procedure

Sets percentage-based resource allocation for each High Priority Consumer Group used by AUTOTASK Clients

DISABLE Procedures

This procedure prevents AUTOTASK from executing any requests from a specified client or operation.

Syntax

Disables all AUTOTASK functionality.

DBMS_AUTO_TASK_ADMIN.DISABLE;

Disables all tasks for the client or operation.

DBMS_AUTO_TASK_ADMIN.DISABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2);

Parameters

Table 32-3 DISABLE Procedure Parameters

Parameter Description

client_name

Name of the client, as found in DBA_AUTOTASK_CLIENT View

operation

Name of the operation as specified in DBA_AUTOTASK_OPERATION View

window_name

Optional name of the window in which client is to be disabled

Usage Notes

  • If operation and window_name are both NULL, the client is disabled.

  • If operation is not NULL, window_name is ignored and the operation is disabled

  • If operation is NULL and window_name is not NULL, the client is disabled in the specified window.

ENABLE Procedures

This procedure allows a previously disabled client, operation, target type, or individual target to be enabled under AUTOTASK control. Specifying the DEFERRED option postpones the effect of the call until the start of the next maintenance window. If IMMEDIATE option is specified the effect of this call is immediate – as long as there is a currently open maintenance window.

Syntax

Re-enabling AUTOTASK. This version enables the specified client. Note that any explicitly disabled tasks or operations must be re-enabled individually.

DBMS_AUTO_TASK_ADMIN.ENABLE;

Re-enabling a client or operation.Note that any explicitly disabled tasks or operations must be re-enabled individually.

DBMS_AUTO_TASK_ADMIN.ENABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2);

Parameters

Table 32-4 ENABLE Procedure Parameters

Parameter Description

client_name

Name of the client, as found in DBA_AUTOTASK_CLIENT View

operation

Name of the operation as specified in DBA_AUTOTASK_OPERATION View

window_name

Optional name of the window in which client is to be enabled

Usage Notes

  • If operation and window_name are both NULL, the client is enabled.

  • If operation is not NULL, window_name is ignored and the specified operation is enabled

  • If operation is NULL and window_name is not NULL, the client is enabled in the specified window.

GET_CLIENT_ATTRIBUTES Procedure

This procedure returns values of select client attributes.

Syntax

DBMS_AUTO_TASK_ADMIN.GET_CLIENT_ATTRIBUTES(
   client_name        IN    VARCHAR2,   service_name       OUT   VARCHAR2,   window_group       OUT   VARCHAR2);

Parameters

Table 32-5 GET_CLIENT_ATTRIBUTES Procedure Parameters

Parameter Description

client_name

Name of the client, as found in DBA_AUTOTASK_CLIENT View

service_name

Service name for client, may be NULL

window_group

Name of the window group in which the client is active

GET_P1_RESOURCES Procedure

This procedure returns percent of resources allocated to each AUTOTASK High Priority Consumer Group.

Syntax

DBMS_AUTO_TASK_ADMIN.GET_P1_RESOURCES(
   stats_group_pct     OUT   NUMBER,
   seg_group_pct       OUT   NUMBER,
   tune_group_pct      OUT   NUMBER,
   health_group_pct    OUT   NUMBER);

Parameters

Table 32-6 GET_P1_RESOURCES Procedure Parameters

Parameter Description

stats_group_pct

%resources for Statistics Gathering

seq_group_pct

%resources for Space Management

tune_group_pct

%resources for SQL Tuning

health_group_pct

%resources for Health Checks

Usage Notes

Values will add up to 100%.

OVERRIDE_PRIORITY Procedures

Note:

This subprogram is deprecated and becomes nonoperative with Oracle Database 12c.

This procedure is used to manually override task priority. This can be done at the client, operation or individual task level. This priority assignment is honored during the next maintenance window in which the named client is active. Specifically, setting the priority to URGENT causes a high priority job to be generated at the start of the maintenance window. Setting priority to CLEAR removes the override.

Syntax

Override Priority for a Client.

DBMS_AUTO_TASK_ADMIN.OVERRIDE_PRIORITY (
   client_name       IN    VARCHAR2,
   priority          IN    VARCHAR2);

Override Priority for an Operation.

DBMS_AUTO_TASK_ADMIN.OVERRIDE_PRIORITY (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   priority          IN    VARCHAR2);

Override Priority for a Task.

DBMS_AUTO_TASK_ADMIN.OVERRIDE_PRIORITY (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   task_target_type  IN    VARCHAR2,
   task_target_name  IN    VARCHAR2,
   priority          IN    VARCHAR2);

Parameters

Table 32-7 OVERRIDE_PRIORITY Procedure Parameters

Parameter Description

client_name

Name of the client, as found in DBA_AUTOTASK_CLIENT View

priority

URGENT, HIGH, MEDIUM or LOW

operation

Name of the operation as specified in DBA_AUTOTASK_OPERATION View

task_target_type

Type of target to be affected, as found in V$AUTOTASK_TARGET_TYPE View

task_target_name

Name of the specific target to be affected

SET_CLIENT_SERVICE Procedure

This procedure associates an AUTOTASK Client with a specified Service.

Syntax

DBMS_AUTO_TASK_ADMIN.SET_CLIENT_SERVICE(
   client_name        IN    VARCHAR2,
   service_name       IN   VARCHAR2);

Parameters

Table 32-8 SET_CLIENT_SERVICE Procedure Parameters

Parameter Description

client_name

Name of the client, as found in DBA_AUTOTASK_CLIENT View

service_name

Service name for client, may be NULL

Usage Notes

All work performed on behalf of the Client takes place only on instances where the service is enabled.

SET_P1_RESOURCES Procedure

This procedure sets percentage-based resource allocation for each High Priority Consumer Group used by AUTOTASK Clients.

Syntax

DBMS_AUTO_TASK_ADMIN.SET_P1_RESOURCES(
   stats_group_pct     OUT   NUMBER,
   seg_group_pct       OUT   NUMBER,
   tune_group_pct      OUT   NUMBER,
   health_group_pct    OUT   NUMBER);

Parameters

Table 32-9 SET_P1_RESOURCES Procedure Parameters

Parameter Description

stats_group_pct

%resources for Statistics Gathering

seq_group_pct

%resources for Space Management

tune_group_pct

%resources for SQL Tuning

health_group_pct

%resources for Health Checks

Usage Notes

Values must be integers in the range 0 to 100, and must add up to 100 (percent), otherwise, an exception is raised.