Skip to Main Content
  • Questions

  • ORA-25153: Temporary Tablespace is Empty

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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