ORA-25153: Temporary Tablespace is Empty

测试现象：

SQL> exec dbms_tts.transport_set_check('USERS',TRUE);

BEGIN dbms_tts.transport_set_check('USERS',TRUE); END;

*

ERROR at line 1:

ORA-25153: Temporary Tablespace is Empty

SQL> select * from dba_objects order by object_name;

select * from dba_objects order by object_name

*

ERROR at line 1:

ORA-25153: Temporary Tablespace is Empty

检查原因：

看了一下DBA_TABLESPACES，嗯...TEMP的status还online....不过是假象。

www.ixdba.net

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

SQL> select tablespace_name, file_name from dba_temp_files;

no rows selected

dba_temp_files里可是空空如也~~

解决问题：

我们会遇到两种情况，一种是OS level的physical datafile不见了，一种是logical tempfile不见了，我不知道我之前曾做了 啥，不过似乎遇到的是第二种情况，验证一下。

$ ls -al

total 11642092

drwxr-xr-x 5 oracle dba 1024 Sep 2 16:43 .

drwxrwxrw- 8 oracle dba 512 Sep 24 17:55 ..

-rw-r----- 1 oracle dba 1925120 Oct 5 13:56 control01.ctl

-rw-r----- 1 oracle dba 1925120 Oct 5 13:56 control02.ctl

-rw-r----- 1 oracle dba 1925120 Oct 5 13:56 control03.ctl

-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 cwmlite01.dbf

-rw-r--r-- 1 oracle dba 415486976 Aug 5 18:01 dba.dmp

-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 drsys01.dbf

-rw-r----- 1 oracle dba 144842752 Oct 5 13:17 example01.dbf

-rw-r----- 1 oracle dba 69476352 Oct 5 13:17 indx01.dbf

-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 odm01.dbf

-rw-r----- 1 oracle dba 52445184 Oct 5 13:17 oem_repository.dbf

-rw-r----- 1 oracle dba 20979712 Mar 3 2004 ram_01.dbf

drwxrjkr-x 2 oracle dba 512 Dec 17 2003 redo01

drwxr-xr-x 2 oracle dba 512 Dec 17 2003 redo02

drwxr-xr-x 2 oracle dba 512 Dec 17 2003 redo03

-rw-r----- 1 oracle dba 20979712 Oct 5 13:17 rman_tbs01.dbf

-rw-r--r-- 1 oracle dba 664 Aug 5 18:03 sqlnet.log

-rw-r----- 1 oracle dba 429924352 Oct 5 13:49 system01.dbf

-rw-r----- 1 oracle dba 99622912 Aug 16 14:16 temp01.dbf

-rw-r----- 1 oracle dba 38019072 Oct 5 13:17 tools01.dbf

-rw-r----- 1 oracle dba 351281152 Oct 5 13:56 undotbs01.dbf

-rw-r----- 1 oracle dba 1048584192 Oct 5 13:17 users01.dbf

-rw-r----- 1 oracle dba 1048584192 Oct 5 13:17 users02.dbf

-rw-r----- 1 oracle dba 47194112 Oct 5 13:17 xdb01.dbf

-rw-r----- 1 oracle dba 5251072 Oct 5 13:17 xyz.dbf

这temp01.dbf datafile最后access时间怎么还停留在8月勒...可见我两个月没上来数据库玩了，都没发现到。不管了，先救回来再说。

SQL>          alter tablespace temp add tempfile '/oradata/elife02/temp01.dbf'

Tablespace altered.

SQL>     select FILE_NAME,TABLESPACE_NAME,STATUS from dba_temp_files;

FILE_NAME TABLESPACE_NAME STATUS

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

/oradata/elife02/temp01.dbf TEMP AVAILABLE

模拟现象：

现在来恶搞，先模拟第一种drop physical datafile

#rm temp01.dbf

SQL>               select file_name , TABLESPACE_NAME, STATUS from dba_temp_files;

FILE_NAME

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

TABLESPACE_NAME STATUS

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

/oradata/elife02/temp01.dbf

TEMP AVAILABLE

此时temp file还是保留 AVAILABLE

SQL> select * from dba_objects order by object_name;

select * from dba_objects order by object_name

*

ERROR at line 1:

ORA-01116: error in opening database file 201

ORA-01110: data file 201: '/oradata/elife02/temp01.dbf'

ORA-27041: unable to open file

SVR4 Error: 2: No such file or directory

Additional information: 3

不过只要有process在进行sorting，就会出现错误了，那此时数据库仍可正常开关机吗？

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 337086968 bytes

Fixed Size 731640 bytes

Variable Size 318767104 bytes

Database Buffers 16777216 bytes

Redo Buffers 811008 bytes

Database mounted.

Database opened.

SQL>              select file_name , TABLESPACE_NAME, STATUS from dba_temp_files;

select file_name , TABLESPACE_NAME, STATUS from dba_temp_files

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '/oradata/elife02/temp01.dbf'

 

显然是可以，不过在开机时候，DBWR会把找不到temp file的信息写入trace file内。

由于我只是砍掉physical file，对于Oracle来说他仍然认为这个tempfile需要存在，故原本我想 依照文件所说，先drop掉仍然存在的metadata，只是当我下这指令时，系统似乎会hang住不动。

SQL> alter database tempfile '/oradata/elife02/temp01.dbf' drop;

....................

我只好先把他offline，再试试看可不可以drop。

SQL> alter database tempfile '/oradata/elife02/temp01.dbf' offline;

Database altered.

SQL> alter database tempfile '/oradata/elife02/temp01.dbf' drop;

^Calter database tempfile '/oradata/elife02/temp01.dbf' drop

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

还是不能drop，好吧，重新启动试试 看。

SQL> startup force

ORACLE instance started.

Total System Global Area 337086968 bytes

Fixed Size 731640 bytes

Variable Size 318767104 bytes

Database Buffers 16777216 bytes

Redo Buffers 811008 bytes

Database mounted.

Database opened.

SQL> alter database tempfile '/oradata/elife02/temp01.dbf' drop;

Database altered.

可以了，我不知道为什么，但后来我不死心又一次砍掉tempfile再立刻drop掉却又是可行的~~

$ rm temp02.dbf

$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0 .5.0 - Production on 星期二 10月 5 14:42:46 2004

Connected to:

Oracle9i Enterprise Edition Release 9.2.0 .5.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0 .5.0 - Production

SQL> alter database tempfile '/oradata/elife02/temp02.dbf' drop;

Database altered. (又可以了)

SQL> alter tablespace temp add tempfile '/oradata/elife02/temp01.dbf' size 5m ;

Tablespace altered.