Re: Tablespace Question

depending on the amount of data in the table in question, if it is fairly small, a quicker way may be to:

export existing table
rename TABLE to TABLE_OLD
create table TABLE tablespace NEW_TS storage (whatever)

   as select * from TABLE_OLD where rownum < 1;      (this keeps you from having to retype all the columns) import exported DATA

   (permissions, indexes, FKs, views, etc are all rebuilt)

Like I said though, it depends on the size of the table. Large tables may take longer to import than it would take to load the new table via direct path inserts from the old table.

In article <Is4L5.127$%X3.113045_at_nnrp1.sbc.net>,   "spencer" <spencerp_at_swbell.net> wrote:
> in Oracle 8.0.6 and earlier, no, there is no statement
> that will change the tablespace for an existing table.
> but you can effectively "move" a table to a different
> tablespace:
>
> create a new table in the desired tablespace, insert the
> rows from the original table into the new table, grant the
> required privileges on the new table, create any triggers,
> rename the original table to a different name, rename the
> new table to the original name, and then recompile any
> views, functions, procedures, and package bodies that
> reference the table.
>
> HTH
>
> "Kian Lee" <ooiklnews_at_yahoo.com> wrote in message
 news:8tigt7$dca$1_at_nnrp1.deja.com...
> > Is there a way to change a table's tablespace??
> >
> > Regards,
> > Kian Lee
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 30 2000 - 12:03:12 CST