DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, August 6, 2015

ORA-25153: Temporary Tablespace is Empty

ORA-25153: Temporary Tablespace is Empty

Temporary table space is TEMP but no data files for TEMP table space

select property_value from database_properties where property_name like ‘%DEFAULT_TEMP_TABLESPACE%';

PROPERTY_VALUE
———————————————
TEMP

select tablespace_name,file_name from dba_temp_files where tablespace_name='TEMP';

no rows selected

Solution: There are many solutions. Use one of the below to fix.

1. First create a new temporary tablespace TEMP2

create temporary tablespace TEMP2 tempfile '+DATA' size 2g autoextend on next 100m maxsize 20g;

2. Set the created TEMP2 tablespace as default temporary tablespace

alter database default temporary tablespace TEMP2;

3. Drop the tablespace TEMP

drop tablespace TEMP including contents and datafiles;

4. Create again a new temporary tablespace TEMP

create temporary tablespace TEMP TEMPfile '+DATA' size 2g autoextend on next 100m maxsize 20g;

5. Set the created TEMP tablespace as default temporary tablespace

alter database default temporary tablespace TEMP;

6. If you need to add extra tempfile to TEMP tablespace

alter tablespace TEMP add tempfile '+DATA' size 2g autoextend on next 100m maxsize 20g

7. Drop the tablespace TEMP2 which we have created at first

drop tablespace TEMP2 including contents and datafiles;

1 comment:

forbes marshall said...

Useful solution

by
Balaji.R