Skip to Main Content
  • Questions
  • ORA-25153: Temporary Tablespace is Empty

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, abdullah.

Asked: August 16, 2017 - 5:36 pm UTC

Last updated: August 17, 2017 - 1:31 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi

I have one production database , and a replica database of it which is open in READ-ONLY mode.
The recovery of the read-only database is done once every night.

I have created a new temporary file on the production environment.

CREATE TEMPORARY TABLESPACE TEMP_NEWSS TEMPFILE '/u06/oradata/imenq/datafiles/temp101.dbf' SIZE 1024M AUTOEXTEND ON;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEWSS;

Now, i have not issued any command on the read-only database for the same. The recovery of the production database is running fine ,
and all the data are intact on read-only database. But , now i am getting the error ORA-25153: Temporary Tablespace is Empty whenever i am running any query on the read only database.


I have tried the following command on read-only instacne :-
alter database rename file '/u06/oradata/imenq/datafiles/temp_new.dbf' to '/u05/oradata/imenq/datafiles/temp101.dbf';

But still , the issue is the same.


select * from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u06/oradata/imenq/datafiles/temp101.dbf
1 TEMPNEW 1073741824 131072 ONLINE
1 NO 0 0 0 1072693248 130944


and Connor said...

Depending on how you do the duplicating, temp files are not transferred to a replica database, and with good reason. Just a waste of resources, because after all, its temporary stuff.

When opening a read only database, you can just have the following in your startup scripts:


alter database tempfile '/u06/oradata/imenq/datafiles/temp101.dbf' drop ;
alter tablespace TEMPNEW add tempfile '/u06/oradata/imenq/datafiles/temp101.dbf' reuse;

or just drop the TEMPNEW tablespace and re-create it. Either will be just about instantaneous because temp files are sparse.


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database