19c ORA-13785: Missing Target Object For Tuning Task “SYS_AUTO_SPM_EVOLVE_TA

郑全

ORA-13785: Missing Target Object For Tuning Task “SYS_AUTO_SPM_EVOLVE_TASK” (Doc ID 2676633.1)

In this Document
Symptoms
Changes
Cause
Solution
APPLIES TO:
Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
ORA-13785: missing target object for tuning task "SYS_AUTO_SPM_EVOLVE_TASK"
OR
ORA-13785: missing target object for tuning task "SYS_AUTO_SQL_TUNING_TASKK"
CHANGES

CAUSE
Missing entry from SYS.WRI$_ADV_TASKS object

Sample output
SELECT ID "TASK_ID", NAME, ADVISOR_NAME, DESCRIPTION
FROM SYS.WRI$_ADV_TASKS
WHERE NAME IN ('SYS_AUTO_SPM_EVOLVE_TASK', 'SYS_AUTO_SQL_TUNING_TASK')
TASK_ID NAME ADVISOR_NAME DESCRIPTION
---------------------------------------- ----------------------- ------------------------
2 SYS_AUTO_SPM_EVOLVE_TASK SPM Evolve Advisor Automatic SPM Evolve Task
1 SYS_AUTO_SQL_TUNING_TASK SQL Tuning Advisor Automatic SQL Tuning Task

SOLUTION
for Symptom of Automatic SPM Evolve Task
SQL> SELECT DBMS_SPM.REPORT_AUTO_EVOLVE_TASK FROM DUAL;
ERROR:
ORA-13785: missing target object for tuning task "SYS_AUTO_SPM_EVOLVE_TASK"

Verify
SELECT ID, TYPE, TASK_ID, ATTR4, ATTR8 FROM SYS.WRI$_ADV_OBJECTS
WHERE TASK_ID IN (SELECT ID FROM SYS.WRI$_ADV_TASKS WHERE NAME = 'SYS_AUTO_SPM_EVOLVE_TASK')
AND ID = 2;   =>>>>>>>>>> Here this number matters, verify this from cause section
Rectify
INSERT INTO SYS.WRI$_ADV_OBJECTS (TASK_ID, ID, TYPE, ATTR8)
VALUES (<SPM_EVOLVE_TASK_ID>,1,25,0);
COMMIT;
EXECUTE DBMS_SPM.RESET_EVOLVE_TASK('SYS_AUTO_SPM_EVOLVE_TASK');
EXECUTE DBMS_SPM.RESUME_EVOLVE_TASK('SYS_AUTO_SPM_EVOLVE_TASK');
Verify if it is working
SELECT DBMS_SPM.REPORT_AUTO_EVOLVE_TASK FROM DUAL;
#########################################################################################
For Symptom of Automatic SQL Tuning Task
SQL> SELECT DBMS_SPM.REPORT_AUTO_EVOLVE_TASK FROM DUAL;
ERROR:
ORA-13785: missing target object for tuning task "SYS_AUTO_SQL_TUNING_TASK"
Verify
SELECT ID, TYPE, TASK_ID, ATTR4, ATTR8 FROM SYS.WRI$_ADV_OBJECTS
WHERE TASK_ID IN (SELECT ID FROM SYS.WRI$_ADV_TASKS WHERE NAME = 'SYS_AUTO_SQL_TUNING_TASK')
AND ID = 1;    =>>>>>>>>>> Here this number matters, verify this from cause section
Rectify
INSERT INTO SYS.WRI$_ADV_OBJECTS (TASK_ID, ID, TYPE, ATTR4)
VALUES (<SQL_TUNING_TASK_ID>,1,22,'SQL Auto Workload Object');
COMMIT;
EXECUTE DBMS_SQLTUNE.RESET_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK');
EXECUTE DBMS_SPM.RESUME_EVOLVE_TASK('SYS_AUTO_SQL_TUNING_TASK');
Verify if it is working
SELECT DBMS_SPM.SYS_AUTO_SQL_TUNING_TASK FROM DUAL;

If tuning pack licence not purchased we will get below error and in this case do not suggest customer to add manual entry on table
SQL> select owner, task_name, execution_start, execution_end from dba_advisor_executions where task_name like '%SYS_AUTO_SQL%';
no rows selected
2 BEGIN dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK'); END;
*
ERROR at line 1:
ORA-13717: Tuning Package License is needed for using this feature.