Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, PRAKASH.

Asked: September 16, 2016 - 11:32 am UTC

Last updated: September 20, 2016 - 4:54 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

Is it possible to move tables from one datafile to another datafile within the same tablespace..?
Could you please suggest me.



Thanks,
Prakash

and Connor said...

Try something like this:

SQL> create table t as select 1 x from dual;

Table created.

SQl> alter table t allocate extent
  2    ( datafile '....' size XXX );

Table altered.


where the datafile is the one you do NOT want to use, and XXX is nearly the freespace available in that datafile. In effect you have now "filled" that datafile.

Now do your 'alter table move' in your real table. It will be moved and cannot use the space in the full datafile. Then drop table t (with the purge option).


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 19, 2016 - 5:59 am UTC


table move into another datafile

A reader, September 19, 2016 - 6:06 am UTC

Hi,
Thank you very much for reply.
I want to move table t1 from datafile1 to datafile2 in same tablespace.
Plz explain in details.

Thanks in advance.
Connor McDonald
September 19, 2016 - 4:40 pm UTC

we already did

Apparently you forgot one thing:

Bill S., September 19, 2016 - 5:41 pm UTC

You forgot the spoon, guys.
Chris Saxon
September 20, 2016 - 4:54 pm UTC

:-)