drop datafile

[dba_admin]

The following example drops one datafile successfully. Why the dictionary still records it?

===========================

1 select tablespace_name, file_name, bytes/1048576 "MB"
2 from dba_data_files
3* where tablespace_name='USERS3'
SQL> /

TABLESPACE_NAME FILE_NAME MB
-------------------- ------------------------------------------------------- -----------
USERS3 C:\ORACLE\ORADATA\ORCL\USERS03X.DBF 1.00
USERS3 C:\ORACLE\ORADATA\ORCL\USERS03Y.DBF 1.00

SQL> alter database datafile 'C:\ORACLE\ORADATA\ORCL\USERS03Y.DBF' offline drop;

Database altered.

SQL> select tablespace_name, file_name, bytes/1048576 "MB"
2 from dba_data_files
3 where tablespace_name='USERS3';

TABLESPACE_NAME FILE_NAME MB
-------------------- ------------------------------------------------------- -----------
USERS3 C:\ORACLE\ORADATA\ORCL\USERS03X.DBF 1.00
USERS3 C:\ORACLE\ORADATA\ORCL\USERS03Y.DBF 1.00

SQL> select name, status from v$datafile where name like '%USER%3%';

NAME STATUS
---------------------------------------- -------
C:\ORACLE\ORADATA\ORCL\USERS03X.DBF ONLINE
C:\ORACLE\ORADATA\ORCL\USERS03Y.DBF RECOVER

[dba_admin]

from Oracle (Helen Schoone ) : " ALTER DATABASE DATAFILE '...' OFFLINE DROP. However, that does not remove the datafile from the database. It introduces a logical inconsistency into the data dictionary as you still have tablespace and free space information which lists that datafile. Data dictionary tables will list it as 'NEEDS RECOVERY.' The primary purpose of the command is to allow a database which is not running in archivelog mode to open if for some reason a datafile was lost at the OS level"

Excellent!

[santoshym]

Hi

I agree with the user above

The best thing for you is to take a logical backup of the data in the other datafiles drop the tablespace and recreate the datfiles and import the data back in the tablespace

Regards

[sreddy]

Drop the datafile if you have the intention of dropping tablespace. Does it sound funny!

Yes! If one has the intention of dropping tablespace, he/she could drop the tablespace very well. Why should one attempt to drop each datafile, then drop tablespace and finally delete datafiles at O/S level.

[pando]

sreddy that command is to enable someone who cannot open a database when he loses a datafile in no archive mode, at least I only use it when that happenes

[sudip]

I think U can shrink the Datafile and then drop it. This way Os level space is saved.

[armel]

the safest way to save disk space at OS level and to keep the database ok is to resize the datafile so small that it will never be allocated ...

[sreddy]

Originally posted by pando
sreddy that command is to enable someone who cannot open a database when he loses a datafile in no archive mode, at least I only use it when that happenes

Thanks Pando! It didn't strike my mind as we deal with no archive mode databases rarely... at work.