Re: How do I delete a data file from a tablespace but still keep its data into other data files of this tablespace?

You can't.

Once a datafile has been added to a tablespace, it's there for good. The best you can do is (if it's empty) resize it to some ridiculously small size.

Alternatively, query dba_segments to see what's in the tablespace. Then export each and every one of those segments. Then drop the tablespace INCLUDING CONTENTS. The re-create the tablespace, and then import from the previous export.

If you've got 8i or above, you can dispense with the export/import routine: just 'alter table X move tablespace Y' for all the tables (assuming only tables are involved), then drop and re-create the tablespace, then move everything back again.

Regards
HJR "Liu, Wallace" <LiuW_at_Sydney.BTAP.BT.com> wrote in message news:aaq3po$bh5$1_at_pheidippides.axion.bt.co.uk...
> Dear Oracle gurus,
>
> I am going to rearrange my Oracle database. My Oracle database runs in
> NOARCHIVE mode. I want to delete one data file from one tablespace. The
> tablespace is only 30% full. How do I remove one data file but keep the
> data(if any stored in this data file) to other part of the tablespace?
>
> Cheers
>
> Wallace Liu (wallace.liu_at_ap.att.com)
>
>
Received on Wed May 01 2002 - 21:57:13 CDT