[Oracle]查看SQL的执行计划 - AUTOTRACE，遇到错误ORA-00904: "OTHER_TAG": 标识符无效

最新推荐文章于 2024-06-25 12:17:20 发布

最新推荐文章于 2024-06-25 12:17:20 发布 · 183 阅读

文章标签：

本文介绍了一种在Oracle数据库中遇到的执行计划显示错误及解决方案。通过重建执行计划表PLAN_TABLE，解决了因表版本过旧导致的显示问题，并展示了正确的执行计划与统计信息。

SQL> SET AUTOTRACE ON
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
--------------
26-9月 -12


执行计划
----------------------------------------------------------
ERROR: an uncaught error in function display has happened; please contact Oracle
 support

       Please provide also a DMP file of the used plan table PLAN_TABLE
       ORA-00904: "OTHER_TAG": 标识符无效


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

解决方法：

Need to recreate or upgrade the plan table, it is still the 8i one.
Please drop the table and run

SQL> drop table PLAN_TABLE ;

表已删除。

SQL> @C:\oracle\product\10.2.0\client_2\RDBMS\ADMIN\utlxplan.sql

表已创建。

SQL> SET AUTOTRACE ON
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
--------------
26-9月 -12


执行计划
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

ps：查看执行计划，也可以是使用 [Oracle]如何查看SQL的执行计划 - DBMS_XPLAN Package