ORA-25153: Temporary Tablespace is Empty 重建控制檔案導致丟失tempfile

parknkjun發表於2014-09-27
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章