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

Howard, that is quasi-true, or literally true by the letter of the law.

But, depending on the number of tablespaces, datafiles and associations and relationships, you must concede that one could do something along the lines of:

tablespace hooterville

   datafile farkel1
   datafile dollyparton1

and one wanted to be rid of farkel1

one could create tablespace arkansas

    datafile dollyparton2

and copy, move, export/import, etc the stuff in hooterville to arkansas, drop tablespace hooterville including contents, and then rebuild it as desired.

I agree, once a datafile, always a datafile, and I've found no legitimate way to be rid of a datafile entry, and I am not fond of illegitimate ways. But that would work, surely, assuming the impact on relationships to applications, other objects and such, were fully understood beforehand. (Apart from the "Will no one rid me of this troublesome Datafile?" approach as discussed above.)

That is why we did not make use of ROTS and RODFS; they are not a very ideal way to manage nearline access, given this issue.

It does help, when you build your database nucleus, to specify MAXDATAFILES to some huge number within the bounds of sanity, though. I've seen people use values like 20, and end up being quite sorry for that decision.

RSH. "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:aaq9vi$fr0$1_at_lust.ihug.co.nz...
> 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 Thu May 02 2002 - 00:32:23 CDT