How to add a deleted datafile again

[deepasona]

The scenario is
I have deleted a datafile belonging to a Tablespace say A using the command

ALTER DATABASE DATAFILE 'a1.dbf' offline drop;

The datafile exists in the same location .Its not physically deleted from the disk.

Now i want to add the datafile again back to the Tablespace.

Please help

[Srinivas_Sharma]

how about this......

ALTER TABLESPACE A
ADD DATAFILE
'/data/disk1/a1.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M
MAXSIZE 500M;

Srini

[Srinivas_Sharma]

NOTE: The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.

I m not sure if u can add the datafile so easily.

if not, then u need to export all the objects in the datafile,

drop the tablespace including contents,

remove the datafile physcially depending on the OS

re-create the tablespace, add the datafile and import the objects..

check out oracle metalink Note:111316.1

Srini

[davey23uk]

cant just drop a datafile from a tablespace, that tablespace is more than likely gone bye bye now.

YOU will have to what the previous poster said. Although you may not be able to export the data if some of it was contained in that datafile

[BHUSHAN]

Hi ,
Try to do incomplete recovery by using backup control file or hot backup before you drop datafile.

Then you can open the database with resetlog.

Well I think this must work.
You will loose some data , which u have added after droping the datafile. Otherwise this must work.


Then take immediate complete backup of ur database.

But this will work with database in archivelog mode only.

Or You can try tablespace point in time recovery also.