ORA-00604 & ORA-25153

启动本地库查看执行计划时出现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;

 

-- 数据库的默认临时表空间为TMEPSQL> 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

 

--切换临时表空间为TEMP01SQL> 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