My Oracle Support Banner

Automatic SQL Tuning Advisor Task Fails With ' ORA-13605: The specified task or object SYS_AUTO_SQL_TUNING_TASK does not exist ' When Ran at a PDB Level (Doc ID 2538576.1)

Last updated on JULY 20, 2024

Applies to:

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

Goal

Goal of this document is to explain why the Auto STA task will fail if attempted to be executed at a PDB level but if you execute outside the auto task and do it manually it will execute at a PDB level.

****Please note we are not saying you cannot execute this manually at PDB level, only the auto task will fail.****

In a PDB, the Automatic SQL Tuning Advisor job is enabled:

SQL> select status from dba_autotask_client where client_name='sql tuning advisor';
STATUS
--------
ENABLED

However, the below code snippet of AUTO_SQL_TUNING_PROG shows the that it is disabled at PDB level.

SQL> select program_action from dba_scheduler_programs where program_name = 'AUTO_SQL_TUNING_PROG';

...
-- check if we are in a pdb,
-- since auto sqltune is not run in a pdb
IF (exec_task AND -- tuning pack enabled
sys_context('userenv', 'con_id') <> -- not in non-cdb
sys_context('userenv', 'con_id') <> 1 ) THEN -- not in root
exec_task := FALSE;
END IF;
-- execute auto sql tuning task
IF (exec_task) THEN
ename := dbms_sqltune.execute_tuning_task(
'SYS_AUTO_SQL_TUNING_TASK');
END IF;

 

 

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.