ora-01114

1)    connect to the server and check the “df –h”

2)    select file_name,bytes/1024/1024||' Mbytes'  from dba_temp_files;

3)    SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

 

                  Here it is observed that there are 2 temporary tablespaces TEMP and TEMP1 of which TEMP is the default  Temporary tablespace. So change the default to the TEMP1.

 

4)    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

5)    drop tablespace TEMP including contents and datafiles;  -- drop the old default TEMP tablespace.

If you have only one temporary tablesapce then you need to create another temporary tablespace  and make it default and then drop the old default TEMP tablespace.

 

2nd process

 

create temporary tablespace TEMPNEW tempfile '/temp/temp.dbf' size 1024m;

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPNEW;

 

 

drop tablespace TEMP including contents and datafiles;