Re: Moving table segment from one datafile to another

In our last gripping episode jdarrah_co_at_my-deja.com wrote:
> You mention dropping datafiles once the segments are moved. This
 would
> be a bad idea. To the best of my knowledge, you cannot remove a
> datafile from a tablespace the best you can do is resize the file to a
> smaller size using "alter database datafile '<filename>' resize to
> <size>" or something like that. I think you are going to be stuck
> reorging the tablespace. If you use partitioning, you have more
> flexibility to reorg with partition exchanges.
>
> In article <91vsb6$1kq_at_nntpa.cb.lucent.com>,
> "Doug Murphy" <douglasmurphy_at_avaya.com> wrote:
> > Is there a way to move a segment of a table from one datafile to
 another
> > without reorganizing the table or the tablespace? We have a huge
 (~160 GB)
> > tablespace where the only thing in some datafiles are segments of
 our
> > largest (~20 GB) table, and we want to drop a few datafiles from the
> > tablespace to be reused elsewhere without having to reorganize the
 table.
> >
> > We are running Oracle 8.0.6 on HP-UX 11.0, if that makes a
 difference.
> > Please contact me directly, in addition to the mail list.
> >
> > Doug Murphy
> > douglasmurphy_at_avaya.com
> >
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

You can drop a datafile from a tablespace, although not with the 'ALTER TABLESPACE' command. One would use the 'ALTER DATABASE DATAFILE ... OFFLINE DROP' command which does remove a datafile from a tablespace by removing it from the database entirely, which is the desired result if I read the initial post correctly.

However, the segments are the table, as stated in another response to this post and, as such, cannot be migrated individually to different tablespaces. It is the whole table or nothing, unless, of course, one chooses to partition the table. With partitioning one can locate specific sections of the table in specific tablespaces although the segments within each partition must remain in the designated tablespace.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/