Hi,
RDBMS version: 11.2.0.1.0 & 11.2.0.4.0
OS: OEL 5 (64-bit)
I am trying to create a SQL tuning set and on doing so get the below exception. Tried to search on forums also with the ORA exception, but not able to get any resolution for this ORA exception.
Following grants were provided:-
GRANT CREATE ANY SQL PROFILE TO SCOTT;
GRANT DROP ANY SQL PROFILE TO SCOTT;
GRANT ALTER ANY SQL PROFILE TO SCOTT;
GRANT ADVISOR TO SCOTT;
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO SCOTT;
grant execute on dbms_spm to SCOTT;
grant administer sql management object to SCOTT;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlprofile_name VARCHAR2(4000);
BEGIN
my_sqltext := 'SELECT * FROM emp';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, user_name => 'SCOTT', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task3', description => 'Demo Task to tune a query');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task3');
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task3', name => 'my_sql_profile'); -- Culprit for the exception
--dbms_output.put_line(my_sqlprofile_name);
END;
/
Error report -
ORA-13786: missing SQL text of statement object "1" for tuning task "my_sql_tuning_task3"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16255
ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7133
ORA-06512: at line 4
13786. 00000 - "missing SQL text of statement object \"%s\" for tuning task \"%s\""
*Cause: The user attempted to accept SQL profile for an object
that has not a SQL text associated to it.
*Action: Check the identifier of the object and retry the operation.
Would really appreciate if someone could point me in the right direction here.
Tia..