ORA-25153: Temporary Tablespace is Empty

在一测试环境做测试的时候发生以下错误：

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>