Re: changing the tablespace for a table

Todd Marshall wrote:
>
> Hi all-
> I need to move a table from one tablespace to another (same user). I
> can't for the life of me find any docs for doing this without
> changing users, which I don't want to do if I don't have to . Any ideas?
>
> -Todd

Export the table you want to move. Then use the indexfile option and rows = N option on an import. This will create a file named in the indexfile parameter that you can edit.

Edit the file to insert a drop table statement. Modify the create table statement to change the tablespace name to the tablespace you want. Also remove remark indicators on the create table lines and comment out any create index lines.

Now run in sql the file you just edited. It will drop your table and recreate it in another tablespace. Now import the data with the ignore = Y option and rows = Y.

Another posibility is to create a new table in the tablespace you want it in with a create table new-tbl-nme as select * from old-tbl-nme.

You will have to then rename old_tbl_nme to old_tbl_nme_save and rename the new tablename to the name you want. Create all grants, synonyms and indexes on the new table that were on the old table.

Hope this gives you an idea. If you need more detail info email me.

Dick Allie. dallie_at_ionet.net Received on Mon Jun 02 1997 - 00:00:00 CDT