ORA 25153 Temporary Tablespace is Empty

Here is the solution of ORA 25153.

If you faced the above error just add more space in the temporary tablespace or add a new tempfile in the temporary tablespace.

How to check temporary tablespace in oracle

Check the temporary tablespace size using the below query.

SQL> select FILE#,NAME,BYTES/1024/1024 "SIZE IN MB" FROM V$TEMPFILE;

Or you can use following query.

SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZE IN MB" from dba_temp_files;

TABLESPACE_NAME                FILE_NAME                                SIZE IN MB
------------------------------ ---------------------------------------- ----------
TEMP                           /u01/app/oracle/oradata/ocppr/temp01.dbf        60

Change the the size of tempfile

To change the tempfile size use the below query.

SQL> alter database tempfile '/u01/app/oracle/oradata/ocppr/temp01.dbf' resize 100m;

Database altered.

Now check again the size of tempfile.

SQL> select FILE#,NAME,BYTES/1024/1024 "SIZE IN MB" FROM V$TEMPFILE;

     FILE# NAME                                     SIZE IN MB
---------- ---------------------------------------- ----------
         1 /u01/app/oracle/oradata/ocppr/temp01.dbf        100

You can see here the size of tempfile is changed from 60 MB to 100 MB.

How to add new tempfile

If your tempfile size if fully used then you need to add new tempfile, use the below command to add new tempfile.

SQL> alter tablespace temp
  2  add tempfile '/u01/app/oracle/oradata/ocppr/temp02.dbf' size 100m;

Tablespace altered.

After adding new tempfile, now you can see the two tempfiles in your database.

SQL>  select FILE#,NAME,BYTES/1024/1024 "SIZE IN MB" FROM V$TEMPFILE;

     FILE# NAME                                     SIZE IN MB
---------- ---------------------------------------- ----------
         1 /u01/app/oracle/oradata/ocppr/temp01.dbf        100
         2 /u01/app/oracle/oradata/ocppr/temp02.dbf        100

So, this is the solution of ORA 25153 error.

I hope you found this article useful, if yes please subscribe our YouTube channel.

Read: How To Get Tablespace Quota Details Of An User In Oracle