Re: move a table

In 8i, 'alter table blah move tablespace X' will do it, if you're talking about moving a table to a different tablespace. In anything other than 8i, the 'create table newblah tablespace X as select * from oldblah' does the deed -but you'll then have to drop oldblah and rename newblah to be the same name as before.

If it's different datafiles within the same tablespace, then you'll have to export the data, drop the original, and re-create it. I don't believe you can force the placing of the initial extent, but you can most certainly direct where all subsequent extents should go with a 'alter table blah allocate extent (datafile 'c:\thisone.dbf')' When you've then laid out where all the extents should go, you run import with ignore=Y, and the data will then be loaded.

None of those commands affect the schema, which is equivalent simply to the User that created the table in the first place (though watch out for 'create table fred.newblah as select * from scott.oldblah', which does affect the schema).

Regards
HJR "Naushi Hussain" <naushi.hussain_at_alliedsignal.com> wrote in message news:3AA95A24.9CE2152C_at_alliedsignal.com...
> I need to move a table from one datafile to a datafile on another drive
> on the same database and keeping the same schema
>
> any ideas?
>
Received on Fri Mar 09 2001 - 17:27:00 CST