Re: Transferring a table from one to another tablespace

In 8.0.5 you are stuck.

The indexes are not a problem. Alter index blah rebuild tablespace B

Tables however cannot be moved easily in 8.0.5. Export and Import won't necessarily help, because import will simply recreate the table in the same tablespace it's come from. You can, however, export, then run import with INDEXFILE=nameoftextfile, and that creates an ASCII text file with remmed out create table statements -just edit the tablespace there, remove the REM lineheaders, run the ascii file as a SQL script, then run import one last time with ignore=Y.

Or, you can do a 'create table newblah tablespace B as select * from oldblah'. That copies all data across into the new table, housed in the new tablespace. When finished, you can drop the old table, and rename the new one. Trouble is, no constraints are brought across (except Not Nulls), and need to be re-created. Plus, the table is a new object -which means all Indexes need to be re-created, and all permissions re-granted. Nasty.

In 8.1.6, it's all very easy: alter table blah move tablespace B. That really does move the old object, so permissions and constraints are preserved. Unfortunately, all the indexes on that table are still pointing at the old table blocks, and hence need to be rebuilt, but that's not too much effort.

Regards
HJR "Nicolas Bronke" <newsgroup_at_trinity.de> wrote in message news:969m4v$90v$01$1_at_news.t-online.com...
> > As this question has a version specific answer, and you can't expect us
 to
> > outline all the differences between the versions, please always include
 the
> > version you are using.
> > Indexes can be moved with alter index rebuild tablespace <new
> > tablespacename>
> >
> > > Is it complicate to transfer a table which is stored on tablespace A
 to
> > > tablespace B?
> > >
> > > Also the indexes from tablespace C to tablespace D?
> > >
> Sorry for forgetting that. I am usng 8.05 and 8.1.6.
>
> Regards
> Nicolas
>
>
Received on Mon Feb 12 2001 - 19:04:15 CST