Skip to content

How to Resolve ORA-13780: SQL statement does not exist

  • by

ORA-13780

Tried to create a SQL tuning task for specific SQL ID, but it failed with ORA-13780.

SQL> select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01') task_name from dual;
select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01') task_name from dual
       *
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 257
ORA-06512: at "SYS.DBMS_SQLTUNE", line 778
ORA-06512: at line 1

ORA-13780 does not complain about the existence of package DBMS_SQLTUNE. ORA-13780 means that the SQL ID you provided cannot be found in memory, it could be a wrong SQL ID or has been cleared from the SQL area.

Solution

First of all, you should make sure the SQL ID is correct and did exist earlier.

BEGIN_SNAP and END_SNAP

To recover SQL ID, we have to specify a snapshot interval of AWR, BEGIN_SNAP and END_SNAP as input arguments in the function CREATE_TUNING_TASK.

DBA_HIST_SNAPSHOT

To know what interval should be used, you may check AWR history in Enterprise Manager Express, or perform a query on DBA_HIST_SNAPSHOT for sure.

column begin_interval_time format a20;
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
select snap_id, begin_interval_time from dba_hist_snapshot order by 1;

In this case, we have a snapshot interval from 16725 to 16728 can be used for the SQL ID.

SQL> select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01', begin_snap => 16725, end_snap => 16728) task_name from dual;

TASK_NAME
--------------------------------------------------------------------------------
SQLTUNE_21zq47mj49f7w_0105_01

The SQL tuning task has been created. Next, we may execute the SQL tuning task without problem.

Leave a Reply

Your email address will not be published. Required fields are marked *