重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: ORA-13785：missing target object for tuning task [打印本页]

作者: ZhangQi 时间: 2025-6-27 19:05
标题: ORA-13785：missing target object for tuning task
案例：
2025-05-03T06:00:02.329745+08:00
Errors in file /u01/app/oracle/diag/rdbms/his/his1/trace/his1_j001_2156875.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_3278"
ORA-13785: missing target object for tuning task "SYS_AUTO_SQL_TUNING_TASK"
ORA-06512: at "SYS.PRVT_ADVISOR", line 3550
ORA-06512: at "SYS.PRVT_ADVISOR", line 934
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14147
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14174
ORA-06512: at "SYS.WRI$_ADV_SQLTUNE", line 8
ORA-06512: at "SYS.PRVT_ADVISOR", line 917
ORA-06512: at "SYS.PRVT_ADVISOR", line 3455
ORA-06512: at "SYS.DBMS_ADVISOR", line 276
ORA-06512: at "SYS.DBMS_SQLTUNE", line 1224
ORA-06512: at line 19

分析：
‌自动调优任务异常‌：如SYS_AUTO_SPM_EVOLVE_TASK 或 SYS_AUTO_SQL_TUNING_TASK 等自动调优任务在执行过程中出现异常，导致目标对象丢失
这是一个固有问题，踢出自动 SQL_TUNNING
建议：

可以关闭sql tuning advisor

-- 关闭自动 SQL 调优任务

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

-- 查看状态

SELECT client_name, status
FROM dba_autotask_client
WHERE client_name = 'sql tuning advisor';