Tuesday, April 19, 2011

ORA-25153: Temporary Tablespace is Empty

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. Once while exporting table through Data Pump ora-25153 occurs.

C:\>expdp system/ramtech@noida tables=neer.test dumpfile=neer_test.dmp logfile=exp_neerlog.log
Export: Release - Production on Tuesday, 19 April, 2011 12:47:37
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release - 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 555
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3487
ORA-06512: at line 1

Cause :: The cause for the ORA-25153 error is because attempt was made to use space in a temporary tablespace with no files (no datafiles defined).

Solution  :: To solve this problem, i just added a temp file (datafiles) to the TEMP tablespace by using ADD TEMPFILE command,

SQL> alter tablespace temp add tempfile 'D:\oracle\oradata\noida\temp02.dbf' size 50m;
Tablespace altered.

If we check and found that TEMP tablespace already has data files, check the default temporary tablespace for all users and your database and set the default temporary tablespace to a valid temporarary tablespace.

To check the default temporary tablespace of the database:
SQL> select property_name, property_value from database_properties;

1 comment:

Anonymous said...