在一测试环境做测试的时候发生以下错误：
SQL> exec dbms_stats.gather_table_stats(ownname=>‘SYS‘,tabname=>‘TT‘,method_opt=>‘for columns size 1 id‘,cascade=>true); BEGIN dbms_stats.gather_table_stats(ownname=>‘SYS‘,tabname=>‘TT‘,method_opt=>‘for columns size 1 id‘,cascade=>true); END; * ERROR at line 1: ORA-25153: Temporary Tablespace is Empty ORA-06512: at "SYS.DBMS_STATS", line 24281 ORA-06512: at "SYS.DBMS_STATS", line 24332 ORA-06512: at line 1 SQL>
查看临时表空间：temp表空间是处于online状态
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE TEST ONLINE 7 rows selected. SQL>
但是没有发现临时文件：
SQL> select name from v$tempfile; --发现临时文件丢失 no rows selected
查看物理文件：文理文件仍然存在
$ ll total 2678324 -rw-r----- 1 ora11 oinstall 10076160 Oct 25 14:39 control01.ctl -rw-r----- 1 ora11 oinstall 10076160 Oct 25 14:39 control02.ctl -rw-r----- 1 ora11 oinstall 363077632 Oct 25 14:33 example01.dbf -rw-r----- 1 ora11 oinstall 52429312 Oct 25 14:33 redo01.log -rw-r----- 1 ora11 oinstall 52429312 Oct 25 14:33 redo02.log -rw-r----- 1 ora11 oinstall 52429312 Oct 25 14:38 redo03.log -rw-r----- 1 ora11 oinstall 587210752 Oct 25 14:38 sysaux01.dbf -rw-r----- 1 ora11 oinstall 922755072 Oct 25 14:38 system01.dbf -rw-r----- 1 ora11 oinstall 62922752 Aug 14 06:02 temp01.dbf -rw-r----- 1 ora11 oinstall 52436992 Oct 25 14:33 test01.dbf
从物理文件的时间戳可以看出temp文件最后一次访问时间
根据时间查看alert日志，发现在对应的时间做个重建控制文件操作，并且在重建控制完成后启动数据库后在alert日志中发现以下内容：
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
说明是重建控制文件后，丢失了临时文件。（rman恢复数据库时也会发生类似现象）
只要把临时文件重新添加即可。
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/db11/temp01.dbf‘;
Tablespace altered.
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ----------------------------------------
1 1727770 25-OCT-16 3 1 ONLINE READ WRITE 62914560 7680 62914560 8192 /u01/app/oracle/oradata/db11/temp01.dbf
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------------------------------------------------------------------
TEMP /u01/app/oracle/oradata/db11/temp01.dbf
SQL> exec dbms_stats.gather_table_stats(ownname=>‘SYS‘,tabname=>‘TT‘,method_opt=>‘for columns size 1 id‘,cascade=>true);
PL/SQL procedure successfully completed.
SQL>
ORA-25153: Temporary Tablespace is Empty
原文：http://www.cnblogs.com/abclife/p/5998675.html