Originally posted by soniaarora
Pando,
This is exactly what I meant. It is not that after u offline a datafile, u HAVE to drop the tablespace. It is only that this is the workaround available to remove the datafile which is not required in the tablespace.
nope.. We can use a workaround to remove the datafile from a tablespace.

Steps:

1. alter database drop datafile <>;
2. alter database backup controlfile to trace;
3. edit the controlfile creation script generated by the above command. Remove the entry of the datafile that we have dropped in step 1.
4. Now, start up the database in nomount state and recreate the control file.
5. Do the recovey if needed using current logfiles.
6. Open the database.

This works fine. But, we need some experiance to do this. Be, careful doing this..

-nagarjuna