ORA-25153: Temporary Tablespace is Empty

Myth丶恋晨 2022-07-13 10:48 175阅读 0赞

### 测试现象： ###

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**][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.

![pixy.gif_x-id_0f874b41-7dae-8644-9eb2-7c59ec9b70f0][]

[www.ixdba.net]: http://www.ixdba.net/
[pixy.gif_x-id_0f874b41-7dae-8644-9eb2-7c59ec9b70f0]: http://img.zemanta.com/pixy.gif?x-id=0f874b41-7dae-8644-9eb2-7c59ec9b70f0

ORA-25153: Temporary Tablespace is Empty

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 ORA-00959: tablespace 'PSAPTEMP' does not exist

相关 ORA-25153：临时表空为空

相关 empty isset is_null

相关 ORA-25153: Temporary Tablespace is Empty

相关 ORA-25153: Temporary Tablespace is Empty

相关 ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

相关 Oracle中的Temporary tablespace的作用

相关 Temporary Tablespaces

相关 How to Shrink the Datafile of Temporary Tablespace

相关 isset、empty、is_null区别