Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-13786: missing SQL text

happy10319Feb 15 2013 — edited Mar 30 2013
Hi,
on 11g R2, connected as sysdba, I have the following errors. Any idea ? Any help ? Thanks before.
SQL> select sql_id, sql_text from v$sql where sql_text like '%SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW%' and sql_text not like '%v$sq
;

SQL_ID
-------------

6d66jzkkyg2sw
SQL_TEXT
--------------------------------------------------------------------------------
SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D W



HERE D.BUSINESS_UNIT LIKE 'IFP%' AND D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGU
AGE_CD='FRA' UNION SELECT  BUSINESS_UNIT, DESCR FROM PS_SP_RECV1_NONVW A WHERE B
USINESS_UNIT LIKE 'IFP%' AND NOT EXISTS (SELECT 'X' FROM PS_SP_RCV1_NONVWLN D WH
ERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA') ORDER BY 1


SQL> variable stmt_task VARCHAR2(64);
SQL>
SQL> EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '6d66jzkkyg2sw');

PL/SQL procedure successfully completed.

SQL>
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_sqltune.accept_sql_profile(:stmt_task);
BEGIN dbms_sqltune.accept_sql_profile(:stmt_task); END;

*
ERROR at line 1:
ORA-13786: missing SQL text of statement object "1" for tuning task "TASK_2767"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16442
ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7544
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7568
ORA-06512: at line 1
SQL> DECLARE
  2    l_sql_tune_task_id  VARCHAR2(200);
  3  BEGIN
  4    l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
  5                            task_name => ':stmt_task',
  6                            name      => 'TEST_PROFILE',
  7                            force_match  => TRUE,
  8                            profile_type => DBMS_SQLTUNE.PX_PROFILE);
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-13605: The specified task or object :stmt_task does not exist for the
current user.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 5878
ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 28
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7544
ORA-06512: at line 4
This post has been answered by ursusca on Feb 15 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 27 2013
Added on Feb 15 2013
4 comments
3,544 views