Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do you change a tables tablespace?

Re: How do you change a tables tablespace?

From: Howard J. Rogers <howardjr_at_www.com>
Date: Tue, 16 Jan 2001 22:32:33 +1100
Message-ID: <3a643139$1@news.iprimus.com.au>

I am prepared to be proved wrong, but I practically guarantee that the 'alter table move tablespace blah' command is new in 8i, not 8.

Other than that, you sum it up succinctly.

Perhaps you might point out that your option 1 preserves permissions, but the Indexes on the table need to be rebuilt; Option 2 requires all indexes and constraints to be re-created, and all permissions to be re-granted, and Option 3 is utterly impossible using normal import commands because import always attempts to create the new table in exactly the same tablespace as the tablespace the segment came from -and if there is one tablespace we can guarantee exists in all databases (and hence will be re-used on import) it's SYSTEM. Accordingly, your Option 3 requires an import to be done in such a way as to create an editable text file, without actually importing the object; the text file needs to be edited to change the tablespace; the text file needs to be run as a piece of DDL, and import then needs to be *re*-run with ignore=y.

Regards
HJR "Myron Wintonyk" <mwintony_at_med.ualberta.ca> wrote in message news:3A6404F8.C36DF515_at_med.ualberta.ca...
> Here are 3 possibilities:
>
> 1. Alter table move tablespace NEW; (requires Oracle 8 and no
> long columns)
>
> 2. create table temp as select * from TABLE;
> rename table TABLE as oldtemp;
> rename table temp as TABLE;
> rebuild indexes
> (requires no long columns).
>
> 3. export the table (exp utility)
> drop the original table
> rebuild the table (empty) in the new tablespace
> import the table (imp uqility).
>
> I've listed the answers from easiest to most difficult. Let us know if
> you need further details on
> option 3.
>
>
> Robert Sherunkle wrote:
>
> > Hi,
> >
> > I have some tables which have been created in the SYSTEM tablespace.
> > How can I move them without losing the data.
> >
> > Thanks In Anticipation
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
Received on Tue Jan 16 2001 - 05:32:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US