ORA-30357: this PL/SQL function cannot be supported for query rewrite ORACLE 报错 故障修复 远程处理

文档解释

ORA-30357: this PL/sql function cannot be supported for query rewrite

Cause: The statement referenced a PL/SQL function that is not marked DETERMINISTIC.

Action: PerfORM one of the following actions:
– Remove the use of the PL/SQL function.
– Mark the PL/SQL function as DETERMINISTIC.
– Disable the REWRITE option on the materialized view. The function should be marked DETERMINISTIC only if it always returns the same result value for any given set of input argument values, regardless of any database state or session state. Do not mark the function as DETERMINISTIC if it has any meaningful side-effects.

ORA-30357: 这个错误表明查询优化无法将PL/SQL函数的内容放入查询计划中。

官方解释

常见案例

一般处理方法及步骤

1、首先，将PL/SQL函数替换为外部函数。例如，替换UTL_RECOMP.COMPILE_SCHEMA_OBJECT函数为DBMS_UTILITY.COMPILE_SCHEMA（OWNER，OBJECT_NAME，1）

2、如果替换无效，可以尝试重新生成索引、数据字典视图，并重新收集统计信息。

3、最后，如果以上方法均无效，则可以通过禁用查询重写功能来解决此错误，ALTER SYSTEM SET QUERY_REWRITE_ENABLED=FALSE SCOPE=BOTH;