Re: Moving tables to a different tablespace

On Thu, 14 Dec 2000, you wrote:
> I forgot to include our version of Oracle. We're on 8.0.2.5.1 and Sun
> Solaris 2.6
> I need to move some tables out of a tablespace into another. Is there
> another option other than dropping the tables and recreating them in the
> new tablespace?
>
> I have exported the user, but if I understand correctly I can import the
> tables into a different user, but not into a different tablespace, or am I
> wrong? I want to keep the owner as it is.
>
> Thanks

SQL> create table <table_name>
2 tablespace <tablespace_name> 
3 as select * from <old_table>;

You will need to drop the old table, rename the new table to the old, and recreate the indexes after the new table has been built.

You may also want to make sure that any default values you have on the columns are added. I don't believe they are carried over in a CTAS (create table as select) statement.

Cheers,
GC
 --
"The opposite of a correct statement is a false statement. But the opposite of a profound truth may well be another profound truth."

                                    --Niels Bohr