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

"RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message news:Hb4A8.1639$6T5.163889_at_bgtnsc05-news.ops.worldnet.att.net...
> 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.

Er, which is exactly what I told him/her to do. I think. Though it's hard to tell given the density of your prose. Summarising the above, I think we have: create new tablespace. Use export/import/move to relocate segments into new tablespace. Drop and re-create original tablespace. Use export/imp/move to relocate segments back into original tablespace. Precisely what I suggested doing.

>
> 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.

Not sure that there is anything to understand beforehand with the 'move' option, since the data is never leaving the database. But yes, using export/import technique, you have to drop the table at one point, and if there is a foreign key constraint, that could prove troublesome.

HJR
> (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 - 21:47:36 CDT