Fwd: Automatic SQL Tuning SYS_AUTO_SQL_TUNING_TASK Fails With ORA-13605 or ORA-13602

Problem Description:
----------------------------
One of the following errors occur in the alert.log and relevant trace file for the job running SQL Tuning Advisor
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_12402"
ORA-13605: The specified task or object SYS_AUTO_SQL_TUNING_TASK does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2735
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLTUNE", line 821
ORA-06512: at line 4 
  
OR
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_4643"
ORA-13602: The specified parameter TARGET_OBJECTS is not valid for task or object 1.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2715
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLTUNE", line 821
ORA-06512: at line 4


Root Cause:
----------------

This may be a result  of missing or corrupted SQL tuning task

You can verify this as follows:

1) Verify the information for task executions:

SQL> select owner, task_name, execution_start, execution_end from dba_advisor_executions where task_name like '%SYS_AUTO_SQL%';

no rows selected


2) Confirm that the execution of  SQL Tuning advisor fails when executed manually

SQL> exec dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');

ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_12402"
ORA-13605: The specified task or object SYS_AUTO_SQL_TUNING_TASK does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2735
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLTUNE", line 821
ORA-06512: at line 4



Solution:
-----------

To resolve this issue, recreate the automatic SQL Tuning task using the standard creation script:

Connect / as sysdba
$ORACLE_HOME/rdbms/admin/execsqlt.sql

Or

Re-run the tuning advisor on the problematic SQL and accept the recommended tuning task. 





Comments

  1. Jack AlmondJuly 4, 2020 at 1:55 AM

    Great.. It helped but my issues was little different .. And found this link usefull as well

    http://www.moreajays.com/2020/07/ora-13605-specified-task-or-object-does.html

    ReplyDelete
  2. vigneshAugust 31, 2025 at 3:25 PM

    I ended up deleting below tasks :
    SYS_AUTO_SPM_EVOLVE_TASK 02-FEB-22 COMPLETED
    SYS_AUTO_SQL_TUNING_TASK 02-FEB-22 COMPLETED

    Now job fails with below error:
    ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_29394"
    ORA-13605: The specified task or object SYS_AUTO_SQL_TUNING_TASK does not exist for the current user.
    ORA-06512: at "SYS.PRVT_ADVISOR", line 3546
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
    ORA-06512: at "SYS.PRVT_ADVISOR", line 7277
    ORA-06512: at "SYS.PRVT_ADVISOR", line 3190
    ORA-06512: at "SYS.DBMS_ADVISOR", line 276
    ORA-06512: at "SYS.DBMS_SQLTUNE", line 1224
    ORA-06512: at line 19

    select CLIENT_NAME,STATUS,CONSUMER_GROUP,ATTRIBUTES from dba_autotask_client where client_name='sql tuning advisor';

    CLIENT_NAME STATUS CONSUMER_GROUP ATTRIBUTES
    -------------------------------- -------- -------------------------------- ----------------------------------------------------------------
    sql tuning advisor ENABLED ORA$AUTOTASK ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

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

    PROGRAM_ACTION
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DECLARE
    ename VARCHAR2(30);
    exec_task BOOLEAN;
    BEGIN
    -- check if tuning pack is enabled
    exec_task := prvt_advisor.is_pack_enabled(
    dbms_management_packs.TUNING_PACK);

    -- 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') <> 0 AND -- 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;

    -- check whether we are in non-CDB or a PDB
    -- auto SPM evolve only runs in a non-CDB or a PDB, not the root.
    IF (sys_context('userenv', 'con_id') = 0 OR
    sys_context('userenv', 'con_id') > 2) THEN
    exec_task := TRUE;
    ELSE
    exec_task := FALSE;
    END IF;

    -- execute auto SPM evolve task
    IF (exec_task) THEN
    ename := dbms_spm.execute_evolve_task('SYS_AUTO_SPM_EVOLVE_TASK');
    END IF;
    END;

    SQL> select owner, task_name, execution_start, execution_end from dba_advisor_executions where task_name like '%SYS_AUTO_SQL%';

    no rows selected

    Same solution for this issue as well .

    ReplyDelete

Post a Comment

Popular Posts