I face with that error while try to set SQL Plan on one of my sql.
This is the one of he published bug.
Bug 10313110 SQL Advisor raises an ORA-13786 when Trying to Implement a Recommended Parallel Profile
If I try to use below syntax its also through error message such as:
Check the current plan advise:
SET LONG 10000000;
COLUMN RECOMMENDATIONS FORMAT A200 WORD_WRAPPED
SET pagesize 0
SET LONG 10000000
SET feed OFF
SET TRIMSPOOL ON
SET linesize 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&TASK_NAME') AS RECOMMENDATIONS FROM DUAL;
Enter value for task_name: SQL_TUNING_123456789
old 1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&TASK_NAME') AS RECOMMENDATIONS
new 1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_1606725746461') AS RECOMMENDATIONS
And then run below
DECLARE
l_sql_tune_task_id VARCHAR2(200);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => 'SQL_TUNING_1606725746461',
force_match => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
END;
/
It may throw below error:
ERROR at line 1: ORA-13786: missing SQL text of statement object "1" for tuning task "SQL_TUNING_1606725746461" 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 line 4
So please try to set your sql plan by using below syntax:
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'NEW_PLAN', task_owner => 'ABC ',replace => TRUE);
HELIOS BLOG 
Bir Cevap Yazın