Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 22 March 2012

ORA-25153-Temporary Tablespace is Empty.

Cause: Unexpected shutdown or Power failure.

Symptom:
Cause Error “ORA-25153-Temporary Tablespace is Empty” while running Procedure, Export or any oracle functions. Once querying dba_temp_files view, there were no temp files being listed. Though the temporary file was residing in the oradata folder and the dba_tablespaces where the tablespace was present. 

Query to check Default and Temporary Tablespace
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
select username,temporary_tablespace,default_tablespace from dba_users where username='HRMS';  --for Particular User
select default_tablespace,temporary_tablespace,username from dba_users;   --for All Users


The Solution:
As it was a temporary tablespace, you could add a tempfile to the same and made the procedure working:

ALTER TABLESPACE temp ADD tempfile ‘D:\ORACLE\ORADATA\ISSCOHR\TEMP02.DBF’ SIZE 40M;

NOTE: Do not give the same name as the existing file in oradata folder TEMP01.DBF. Let it as it is:

0 comments:

Post a Comment