| Author's name: Allan W. Tham
Author's Email: allanwtham@hotmail.com |
Date written: Feb 8 2002 Oracle version(s): 8.1.7.0.0 |
| I lost a datafile that belongs to TEMP tablespace, how do I recover it? | |
This is the simplest form of recovering of a lost of datafile since in temporary tablespace, there is no permanent objects are stored there!!
1. Offline the tablespace
In archivelog - alter database datafile xxx offline immediate In nonarchivelog - alter database datafile xxx offline drop
2.Drop the tablespace
Drop tablespace xxx
3.Remove physical files of the tablespace and recreate them
Further reading: Metalink Note:136180.1 How to Recover a Temporary Tablespace