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;

 

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