08 March 2007

Solution Of "ORA-25153: Temporary Tablespace is Empty"

Today I have struggled a problem while importing a dump file to oracle 9.2.0.7 database. The problem was about "ORA-25153: Temporary Tablespace is Empty". I investigated the problem and find a solution...
Accidentally creating two temporary tablespace(TEMP and TMP), making first one default(TEMP),without having any datafiles on it(TEMP). Dropping first one making default second one solved the problem.

SQL> SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';
TABLESPACE_NAME
------------------
TEMP
TMP


SQL> SELECT property_value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-----------------
TEMP


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;
Database altered.


SQL> SELECT property_value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-------------------
TMP


SQL> SELECT file_name, tablespace_name FROM DBA_TEMP_FILES;
FILE_NAME                         TABLESPACE_NAME
--------------------------------- -------------------------
/data07/oradata/ashp02/tmp01.dbf  TMP


SQL> drop tablespace TEMP;
Tablespace dropped.


SQL> SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';
TABLESPACE_NAME
-----------------
TMP


SQL>

No comments: