ORA-25153: Temporary Tablespace is Empty

测试现象：

ORA-25153: Temporary Tablespace is Empty

 

检查原因：

检查一下DBA_TABLESPACES, TEMP的status还是online，不过是假象

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                                              STATUS

------------------------------------------------------------ ------------------

SYSTEM                                                       ONLINE

UNDOTBS1                                                     ONLINE

TEMP                                                         ONLINE

CWMLITE                                                      ONLINE

DRSYS                                                        ONLINE

EXAMPLE                                                      ONLINE

INDX                                                         ONLINE

ODM                                                          ONLINE

TOOLS                                                        ONLINE

USERS                                                        ONLINE

XDB                                                          ONLINE

 

TABLESPACE_NAME                                              STATUS

------------------------------------------------------------ ------------------

APP                                                          ONLINE

APP2                                                         ONLINE

UTILS                                                        ONLINE

 

14 rows selected.

 

SQL> select tablespace_name,file_name from dba_temp_files;

 

no rows selected

dba_temp_files里可是空空如也

 

问题解决：

我们会遇到两种情况，一种是OS level的physical datafile不见了，一种是logical tempfile不见了，我这里似乎遇到的是第二种情况，验证一下。

 

[oracle@ORA9ISERV ~]$ cd $ORACLE_BASE

[oracle@ORA9ISERV oracle]$ cd oradata/orcl

[oracle@ORA9ISERV orcl]$ ls -al

total 1937736

drwxr-xr-x 3 oracle oinstall      4096 Jun 16 15:01 .

drwxr-xr-x 3 oracle oinstall      4096 Apr 18 08:45 ..

-rw-r----- 1 oracle oinstall 134225920 Jun 16 15:42 app01.dbf

-rw-r----- 1 oracle oinstall  10493952 Jun 16 11:43 app201.dbf

drwxr-xr-x 2 oracle oinstall      4096 Jun 16 11:43 arch

-rw-r----- 1 oracle oinstall   2023424 Jun 16 15:46 control01.ctl

-rw-r----- 1 oracle oinstall   2023424 Jun 16 15:46 control02.ctl

-rw-r----- 1 oracle oinstall   2023424 Jun 16 15:46 control03.ctl

-rw-r--r-- 1 oracle oinstall  20979712 Jun 16 11:43 cwmlite01.dbf

-rw-r--r-- 1 oracle oinstall  20979712 Jun 16 11:43 drsys01.dbf

-rw-r--r-- 1 oracle oinstall 156639232 Jun 16 11:43 example01.dbf

-rw-r--r-- 1 oracle oinstall  26222592 Jun 16 11:43 indx01.dbf

-rw-r--r-- 1 oracle oinstall  20979712 Jun 16 11:43 odm01.dbf

-rw-r----- 1 oracle oinstall 104858112 Jun 16 11:43 redo01.log

-rw-r----- 1 oracle oinstall 104858112 Jun 16 11:43 redo02.log

-rw-r----- 1 oracle oinstall 104858112 Jun 16 15:46 redo03.log

-rw-r--r-- 1 oracle oinstall 398467072 Jun 16 15:41 system01.dbf

-rw-r--r-- 1 oracle oinstall  41951232 Apr 18 08:47 temp01.dbf

-rw-r--r-- 1 oracle oinstall  10493952 Jun 16 11:43 tools01.dbf

-rw-r--r-- 1 oracle oinstall 209723392 Jun 16 15:46 undotbs01.dbf

-rw-r--r-- 1 oracle oinstall  26222592 Jun 16 11:43 users01.dbf

-rw-r----- 1 oracle oinstall 536879104 Jun 16 15:02 utils.dbf

-rw-r--r-- 1 oracle oinstall  47194112 Jun 16 11:43 xdb01.dbf

 

这里的temp01.dbf datafile最后access时间怎么还停留在4月份，由于是虚拟机，我也没怎么注意。

 

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

 

Tablespace altered.

 

SQL> select file_name,tablespace_name,status from dba_temp_files;

 

FILE_NAME                                                    TABLESPACE_NAME STATUS

------------------------------------------------------------ --------------- ------------------

/u01/app/oracle/oradata/orcl/temp01.dbf                      TEMP            AVAILABLE

 

完成