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;
Thanks for posting this, Ian! I just had the same issue, and I couldn’t find any matching DocID on MyOracleSupport (MOS), so I submitted a Service-Request, and quoted this blog-post of yours, asking Oracle Support if your workaround was the valid fix. They provided me the right solution, which is a little bit different. There is an “official” matching Support DocID to be published and available soon: ORA-13785: Missing Target Object For Tuning Task “SYS_AUTO_SPM_EVOLVE_TASK” ( Doc ID 2676633.1 ). But in the meantime, if anybody has this issue, they can quote my Service Request SR 3-23377950281, or contact me directly for the solution.
Thank you for your reply, but unfortunately the doc ID doesn’t exist anymore and the only doc ID (287918.1) mentioning ORA-13785 gives no solution.
Was the solution given by Oracle much different?
Glad you got it fixed.