Attempted to create SQL Profile for tuning purposes using the Oracle Tuning Advisor. The Oracle Tuning Advisor automatically generates the following script to produce the SQL Profile for the tuning task.
execute dbms_sqltune.accept_sql_profile(task_name => <auto></auto>, replace => TRUE);
However, running the SQL results in the following error.
ORA-13605: The specific task or object<auto></auto>does not exist for the current user.
Toads Oracle Tuning Advisor would create a task and delete it right away. The issue is the following code that the Oracle Tuning Advisor also executes.
Dbms_Sqltune.DROP_TUNING_TASK(<auto></auto>);
WORKAROUND:
1. Run a SQL Monitor and captured the background query for executing the Oracle Tuning Advisor on a query.
2. Run Oracle Tuning Advisor.
3. Get the background code Toad generated from SQL Monitor.
4. Look for the following lines and delete it.
:suggestion := Dbms_Sqltune.REPORT_TUNING_TASK(, TEXT, TYPICAL);
:script := Dbms_Sqltune.SCRIPT_TUNING_TASK(task_name=>);
Dbms_Sqltune.DROP_TUNING_TASK();
5. Executed the edited SQL from SQL Monitor in the Toad Editor.
6. Execute the DBMS_SQL TUNE Script that Oracle Tuning Advisor generated:
execute dbms_sqltune.accept_sql_profile(task_name => , replace => TRUE);
STATUS:
Issue fixed in version 9.5.0. The latest version of Toad for Oracle can be downloaded athttps://support.quest.com/SUPPORT/index?page=home
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center