ORA-00604 & ORA-25153_ora00604 25153 06512-CSDN博客

本文链接：https://blog.csdn.net/lwei_998/article/details/6327889

本文详细记录了解决Oracle数据库中出现的ORA-00604和ORA-25153错误的过程。通过检查并确认临时表空间配置，发现默认临时表空间数据文件丢失是问题根源。经过调整默认临时表空间为TEMP01，并通过设置autotrace查看执行计划，成功解决了执行计划错误。本文提供了从错误诊断到解决方案的完整步骤。

摘要由CSDN通过智能技术生成

启动本地库查看执行计划时出现ORA-00604，ORA-25153错误。原来是临时表空间数据文件丢失引起的。

下面重现一下解决过程：

SQL> select count(1) from LOCATIONS;
COUNT(1)
----------
37
已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
ERROR:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-25153: 临时表空间为空

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
统计信息
----------------------------------------------------------
          9 recursive calls
          0 db block gets
         60 consistent gets
          0 physical reads
          0 redo size
        430 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed


SQL>col PROPERTY_NAME for a25;
SQL>col PROPERTY_VALUE for a20;

-- 数据库的默认临时表空间为TMEP
SQL> SELECT PROPERTY_NAME , PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES P
3 WHERE P.PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                       PROPERTY_VALUE
------------------------------------    --------------------
DEFAULT_TEMP_TABLESPACE   TEMP

--但没有TEMP表空间对应的数据文件
SQL> col file_name for a50;
SQL> col tablespace_name for a6;
SQL> SELECT file_name ,Tablespace_name FROM DBA_TEMP_FILES;

FILE_NAME TABLES
-------------------------------------------------- ------
D:/ORACLE/LIANGWEI/ORADATA/LIANGWEI/TEM01.DBF TEMP01

--切换临时表空间为TEMP01
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;
Database altered

-- 再次用 set autotrace on 查看执行计划，ok。
SQL> select count(1) from LOCATIONS;

COUNT(1)
----------
0

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

------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| LOCATIONS |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------

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