It is not worth the while to drop and recreate the TEMP tablespace, because it will be fragmented pretty badly again. You could only achieve a v.minor performance boost.

Any how follow these steps, you would be fine:


1. Mount the database

2. Query the V$recovery_file and V4datafile to note the filenames that correspond to the file numbers:
select * from v$recovery_file;

3. Alter the database to take the file off line and drop

alter database datafile '.../TEMP01.dbf' OFFLINE DROP;


4. alter database open;


7. Drop the TEMP tablespace

select * from v$tablespace;

drop tablespace temp;

8. Create your temprory tablespace as:

CREATE tablespace temp datafile
'.../TEMP01.dbf' SIZE 500K REUSE;

9. Now you are all set.

Good luck,
Sam

[Edited by sambavan on 04-11-2001 at 09:35 PM]