can u delete a datafile

[sharmila]

how can one delete a single datafile from a database .. is it possible

regards
sharmila

[SANJAY_G]

Yeah.. Drop tablespace and remove the datafile using os command.

Sanjay

[sandycrab]

Sanjay...what will happen if my Tablespace have more then one Datafiles and I want to remove only one?
Anyway...In that case
1. Take tablespace offline
2. alter database datafile datafile_name/Number offline drop
3. Remove datafile using OS command.
Hope it helps...
Sandy

[pando]

wrong

you can never ever remove a datafile from a tablespacw without dropping the tablespace

[Sureshy]

Pando is right..

The datafile metadata will still be in the dictionary.

You could create a new tablespace with desired datafiles.
Move objects to new tablespace.
drop old tablespace.

[Fiza15]

Lets say we create new Tablespace T1 with two datafiles (D1 and D2)...and now I want to transport all data from old tablespace to new one but only in D1want to keep D2 empty for later use...Thanks

[jmodic]

If we put aside the fact that for this case it would be much more natural to create a new tablespace with a single datafile D1, and add the other datafile D2 only after you've moved all your data to D1, there are a couple of ways to force all transported data to reside only in D1:

a) Resize D2 to such a small size that none of the moved objects will fit into it. After you've moved all your data to D1, resize D2 back to its original size.

b) Lets say both of your files are of size 100M. Create a dummy table of size 100M (actually it will have to be a bit smaller, because few blocks are allready occupied by the file header), so that it fills the entire file. Check in which file it got created. If it is in D2 you are all set, if it was in D1 you'll have to create another dummy table that will fill datafile D2 and drop the first dummy table in file D1. Now move your data in the new tablespace, it will all go in D1. At the end, drop the dummy table residing in file D2.

[sreddy]

Originally posted by pando
wrong

you can never ever remove a datafile from a tablespacw without dropping the tablespace

If you do, you are not far from sleepless nights (assuming you don't have right backup in place) . My co-worker was there couple of years ago... the cost of it was 36hours 2 DBAs effort and 200 Users sitting idle. Of course! it was Government Site . Who cares really !

[stmontgo]

specifically if you drop OFFLINE drop a datafile from a tablespace with more than one datafile FET$ will still show that there are available extents in the OFFLINE'd file. Next time it needs to allocate an extent to the TS it might try and allocate one to the offline'd file and whamo! end of story

steve