The Oracle (tm) Users' Co-Operative FAQ

I've got a corrupted data file, and Oracle won't start. I don't need the data so how do I open the database and drop data file?

Back to index of questions

There is no easy way as to drop a datafile of a tablespace. The only way to remove a datafile from a database is to drop the defining tablespace. There are a few steps to follow:

If you are running in Noarchivelog mode

1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline drop
3. open the database - alter database open 
4. check all objects belong to that tablespace: 
       select owner, segment_name, segment_type
       from dba_segments
       where tablespace_name='tbs_name'
5. export out all the objects in that tablespace
6. drop the tablespace - drop tablespace tbs_name including contents
7. Delete the physical datafiles belonging to the tablespace
8. Recreate the tablespace, import back the objects 

If you are running in Archivelog mode

1. mount the database - startup mount 
2. drop the datafile - alter database datafile xxx offline

(Note: the datafile is still part of the database and is marked only as offline in the controlfile. Just make sure you don't use the same data file name again)

3. Remove the physical data file at OS level 
4. open the database - alter database open
5. At the right time, you can 
	export the objects belong to this tablespace, 
	drop the tablespace, 
	create back the tablespace with appropriate datafiles and 
	import the objects back.

Addendum (28th Jan 2002)

Michael.Trothe@originenergy.com.au offers the following: There is another way. This will only work if there is no data in the file that you really require.

1. mount the database 
2. alter the datafile offline drop 
3. delete the file from the sys.file$ table. 

This will prevent it from being recognised when you do hot backups and not allowing you to place the tablespace in to backup mode.

4. open the database in restricted mode then delete any objects that have references to the missing datafile. 
5. shut the database down 
6. startup nomount the database 
7. recreate the control file and alter database open resetlogs. 

All reference to the missing datafile should be gone.

Editor's note: Since this strategy involved direct modification of the data dictionary, your database would no longer be supported by Oracle Corp.

Further reading: Metalink Note:111316.1 How to 'DROP' a Datafile from a Tablespace

Back to top

Back to index of questions