ORA-13785: missing target object for tuning task “SYS_AUTO_SPM_EVOLVE_TASK”

A small walkthrough in ‘clearing’ the ORA-13785 error. This might be in the alert log or when you run DBMS_SPM.REPORT_AUTO_EVOLVE_TASK without and object ID. It probably defaults with object ID ‘1’, which is the last run evolve task(?).

The ‘SYS_AUTO_SPM_EVOLVE_TASK’ ‘root object ID’ (I’m not sure if it is called like this) seemed missing, adding it and resetting and resuming SPM resolved the error. This is all done with trial and error, cross checking a working 12c database and a lot of common sence. BACKUP FIRST! It also includes ‘recreating’ the ‘root object ID’ for ‘SYS_AUTO_SQL_TUNING_TASK’ which I found also missing.

-- Results in "ORA-13785: missing target object for tuning task "SYS_AUTO_SPM_EVOLVE_TASK""
SELECT DBMS_SPM.REPORT_AUTO_EVOLVE_TASK FROM DUAL;

-- Find task ID of SPM and SQL advisor:
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');

-- Find 'root' object ID's (=1) of these advisors
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 IN ('SYS_AUTO_SPM_EVOLVE_TASK', 'SYS_AUTO_SQL_TUNING_TASK')
) AND ID = 1;

-- Insert missing 'root' objects of these advisors
-- Only change the TASK_ID found in first query

--'SYS_AUTO_SPM_EVOLVE_TASK':
INSERT INTO SYS.WRI$_ADV_OBJECTS (TASK_ID, ID, TYPE, ATTR8)
  VALUES (<SPM_EVOLVE_TASK_ID>,1,25,0);

--'SYS_AUTO_SQL_TUNING_TASK'
INSERT INTO SYS.WRI$_ADV_OBJECTS (TASK_ID, ID, TYPE, ATTR4)
  VALUES (<SQL_TUNING_TASK_ID>,1,22,'SQL Auto Workload Object');

COMMIT;

-- For missing 'SYS_AUTO_SPM_EVOLVE_TASK' root object, also run:
EXECUTE DBMS_SPM.RESET_EVOLVE_TASK('SYS_AUTO_SPM_EVOLVE_TASK');
EXECUTE DBMS_SPM.RESUME_EVOLVE_TASK('SYS_AUTO_SPM_EVOLVE_TASK');

-- Not sure if the 'SYS_AUTO_SQL_TUNING_TASK' must be 'reset' as well...

-- Now running the report should result in a report with 'COMPLETED' of last report status
SELECT DBMS_SPM.REPORT_AUTO_EVOLVE_TASK FROM DUAL;

Happy fixing.