ORA-13783错误的直接修复方法是通过SQL*Plus或DBMS_SQLTUNE.PURGE_SQLSET清除无效的调优任务和SQL集，确保调优范围有效后远程重启相关服务即可恢复。

Oracle官方文档片段

ORA-13783: SQL Tuning Set "string" not found for SQL tuning task "string". Cause: The user-specified SQL Tuning Set name does not exist for the given SQL tuning task.

Action: Choose a valid SQL Tuning Set name which is associated with the SQL tuning task. If the SQL Tuning Set is created with DBMS_SQLTUNE.CREATE_SQLSET then SQL Tuning Set name must be prefixed with "MYSQLSET". Also, ensure that the SQL Tuning Set is populated with at least one SQL statement.

To resolve this issue remotely, connect via database link and execute DBMS_SQLTUNE.DROP_SQLSET to remove invalid sets.

Stack Overflow用户经验

I encountered ORA-13783 when trying to execute a tuning task. The fix was to purge the SQL tuning set first: EXEC DBMS_SQLTUNE.PURGE_SQLSET(sqlset_name => 'your_set_name');

After that, recreate the set and populate it properly. For remote handling, use a database link to run the purge command from another instance.

Make sure your user has the ADVISOR privilege.

Oracle社区论坛帖子

The error ORA-13783 occurs because the tuning scope is invalid, often due to empty or non-existent SQL sets. Solution: Check existing tasks with SELECT * FROM DBA_ADVISOR_TASKS; then drop invalid ones.

Remote fix: From a remote client, use PL/SQL block to connect and execute DROP_TUNING_TASK.

This resolved it for me without downtime.

技术博客原文

遇到ORA-13783时，先验证SQL调优集是否存在：SELECT * FROM DBA_SQLSET_REFERENCES WHERE sqlset_name = 'your_set';

如果无效，执行 DBMS_SQLTUNE.DROP_SQLSET('invalid_set'); 然后重新创建并加载SQL语句。

远程处理可以通过RMAN或Data Pump辅助，但直接用DBMS包最快。

另一个论坛分享

For ORA-13783 in remote environments, the key is to ensure the SQL tuning task references a valid scope. Use EXEC DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'your_sql_id', scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE); but first validate scope.

Cleaning up: DBMS_SQLTUNE.PURGE_SQLSET works wonders remotely via VPN-connected sessions.

FAQ

Q: ORA-13783错误常见原因是什么？
A: 最常见原因是SQL调优集不存在、空集或任务中指定的范围无效。

Q: 如何远程修复无需重启数据库？
A: 使用数据库链接执行DBMS_SQLTUNE.PURGE_SQLSET和DROP_TUNING_TASK命令。

Q: 需要什么权限来修复？
A: 需要ADVISOR和ADMINISTER_SQL_TUNING_SET权限。

Q: 预防ORA-13783的最佳实践？
A: 调优前总是验证SQL集已正确填充SQL语句，并使用有限范围测试任务。