Re: Moving tables from one tablespace to another

cfischer_at_ancillary.utmrad1.utmb.edu wrote:

>I have been given the task of upgrading/moving an application. The
>tables are currently in Oracle v6 database but they are all in the
>system tablespace. I need to move them to an Oracle7 db on another
>machine and I need to put the tables in an application data tablespace
>and the indexes in an index tablespace. Everything is under one user. I
>know I can export the user but when I import it it wants to put it into
>the system tablespace again. How do I convince it that that is not the
>right thing to do? Do I have to edit the export file and change ALL the
>references?(Yuk)
 

>please post or send replies to cfischer_at_ancillary.utmrad1.utmb.edu
 

>Thanks

You have at least three considerations here:

1. moving the data.
2. changing the location of the tables.
3. changing the structure of the tables.

You could move the data by pre-creating the tables in the proper tablespace and selecting the data from one instance to another via SQL*Net. This would actually take care of all three considerations.

This would also move the data from an import file as long as the owner is not SYS or SYSTEM. If that is the case, you really need to do as described above.

In any case you best bet is to write SQL scripts that will create CREATE TABLE statements, putting the tables in the correct tablespaces. You can also take care of structure conversion at this same time (CHAR->VARCHAR2).

Use this opportunity to optimize and convert, not merely as moving the data. You would really be doing this anyway, so why not do it right?

Wayne Balmer, WAINWRIGHTS, inc. wbalmer_at_op.net Received on Mon Jan 15 1996 - 00:00:00 CET