ORA-25153:Temporary Tablespace is Empty

今天,执行下面语句时,报错:
SQL> set autotrace on explain;
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-25153: Temporary Tablespace is Empty

SP2-0612: Error generating AUTOTRACE EXPLAIN report

 

上网查资料,看到这样一句话:"我数据的版本是10.2.0.1，之前做过控制文件恢复，导致临时表空间为空，|这是10g的一个bug，在11g里已经没有这个问题"

 

查看临时文件:

SQL> select tablespace_name,file_name from dba_temp_files;

未选定行

 

查看临时表空间:

SQL> select tablespace_name,status
  2  from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE   

 

解决办法:

给临时表空间添加数据文件:

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' reuse;

 

再执行上面的语句,不再报错了.