ORA-13605: The specified task or object does not exist for the current user

ORA- Error:

SQL>Begin
dbms_sqltune.Execute_tuning_task (task_name => 'sql_sta_01151548_85fakm008r9z9');
end;
/

ERROR:
ORA-13605: The specified task or object sql_sta_01151548_85fakm008r9z9 does not exist for the current user.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 5900
ORA-06512: at "SYS.DBMS_SQLTUNE", line 926
ORA-06512: at line 1


Cause/Solution:

I.  The User which is logged into db to create the tuning task doesn't have advisor privilege ; Grant advisor,administer sql tuning set to solve the error

SQL>grant advisor to scott;

SQL>grant administer sql tuning set to scott;

II.  ORA-13605 caused due to ORA-13780 ; as sql statement itself not found in the workload history while creating a tuning task.

SQL> DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id =>'85fakm008r9z9',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => 'sql_sta_01151548_85fakm008r9z9',
description => 'Task to tune a query autbywjufccpd');
END;
/ 

ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655
ORA-06512: at line 4


ORA-13780 can be solved by specifying the AWR begin_snap  and end_snap id in create tuning task code to allow tuning task to search for the sql statement in specific range of workload where it exists. 

SQL> DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap => 291940,
end_snap => 292266,
sql_id =>'85fakm008r9z9',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => 'sql_sta_01151548_85fakm008r9z9',
description => 'Task to tune a query autbywjufccpd');
END;
/ 

PL/SQL procedure successfully completed.



Once tuning task is created successfully, execute the tuning task to get desired result.


SQL>Begin
dbms_sqltune.Execute_tuning_task (task_name => 'sql_sta_01151548_85fakm008r9z9');
end;
/

PL/SQL procedure successfully completed.

Retrieve the Advisory Report ..

SQL>set long 900000
SQL>set linesize 9000
SQL>set pages 9000
SQL>col TUNING_ADVICE_85fakm008r9z9 for a9000
SQL>select dbms_sqltune.report_tuning_task('sql_sta_01151548_85fakm008r9z9') TUNING_ADVICE_85fakm008r9z9 from dual;

PL/SQL procedure successfully completed.

Solved !!!