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)

    Assuming that you are importing these tables into an account other than sys or system, first alter the account so it has no quota on the SYSTEM tablespace:

   ALTER USER <table_owner> quota 0 on SYSTEM;

Next, set the default tablespace for the account to the tablespace you want the tables imported into:

   ALTER USER <table_owner> default tablespace <destination_tablespace>;

(Note: you can do these as one command)

  Now, when you perform the import, Oracle should see that it cannot create the tables in the tablespace they previously resided in (ie. SYSTEM) and it should go to the default tablespace instead.

Hope this helps.

Loren Received on Tue Jan 09 1996 - 00:00:00 CET