A quite sometimes ago my friend asking about how to delete a datafile.
He want to delete datafiles in a tablespace, because he wrongly added datafile into tablespace
He already delete datafile manually, before delete, datafile state is change to offline. After delete datafile manually, database won’t open. And worst of the worst he did that during office hour.
following are steps that help him :
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\DATAFILE\USER01.DBF' OFFLINE DROP; ALTER DATABASE OPEN;
as for information Oracle that he is using is Oracle 9i. in Oracle 10gR2 above, Delete datafile can be done on the fly using
ALTER TABLESPACE users DROP DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\DATAFILE\USER01.DBF'