ORA-25153: Temporary Tablespace is Empty 重建控制文件导致丢失tempfile

最新推荐文章于 2024-09-27 15:00:15 发布

转载最新推荐文章于 2024-09-27 15:00:15 发布 · 231 阅读

本文记录了一次在使用Oracle数据库进行数据导出时遇到ORA-25153错误的过程及解决方案。通过检查发现临时表空间为空，并通过查看alert.log日志进一步定位问题原因，最终通过重新添加临时文件解决了该问题。

expdp jzh/jzh dumpfile=emp1.dmp directory=test_dir tables=emp1 logfile=emp1.log
Export: Release 11.2.0.3.0 - Production on Sat Sep 27 14:40:07 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 724
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3964
ORA-06512: at line 1
导出数据时遇到以上报错，于是先看看temp表空在不在。
SYS@test>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
SYS@test>select tablespace_name,file_name from dba_temp_files;---临时文件没有了
no rows selected
[oracle@11g test]$ ll
total 1955416
-rw-r----- 1 oracle oinstall 10076160 Sep 27 14:44 control01.ctl
-rw-r----- 1 oracle oinstall 362422272 Sep 27 12:12 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 27 14:42 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Sep 27 12:12 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep 27 12:12 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Sep 27 14:40 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Sep 27 14:41 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Sep 25 22:14 temp01.dbf
-rw-r----- 1 oracle oinstall 99622912 Sep 27 14:40 undotbs01.dbf
-rw-r----- 1 oracle oinstall 15736832 Sep 27 14:40 users01.dbf
临时文件在，很奇怪，查查alert.log。
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future rele
ase
Successful mount of redo thread 1, with mount id 2157453076
Completed: CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future rele
ase
Successful mount of redo thread 1, with mount id 2157453076
Completed: CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
这时想起来前几天重建了控制文件，add回来。
SYS@test>alter tablespace temp add tempfile
2 '/u01/app/oracle/oradata/test/temp01.dbf';
Tablespace altered.
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------
TEMP /u01/app/oracle/oradata/test/temp01.dbf

ok了，回来了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-1283074/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10271187/viewspace-1283074/