Re: Using COPY to rebuilding database.

I don't quite understand the question. Why 2 databases? I will assume that you have a Schema (i.e. AP) and all of AP's tables were created in the SYSTEM tablespace. I will also assume that you are basically trying to move the tables out of the SYSTEM tablespace and into a tablespace call AP_DTS(data) and AP_ITS(index).

Easy..

1) Export user AP. (INDEXES=Y ROWS=Y GRANTS=Y)
2)  drop all of AP's objects from the database
3)  ALTER USER AP DEFAULT TABLESPACE AP_DTS

    QUOTA UNLIMITED ON AP_DTS
    QUOTA UNLIMITED ON AP_ITS;
4) import user AP from export file generated in step 1 (INDEXES=N IGNORE=N)
     This will build all of the tables for the Schema AP in the default tablespace (AP_DTS)
5) ALTER USER AP DEFAULT TABLESPACE AP_ITS; 6) import the file again using (NDEXES=Y ROWS=N IGNORE=Y)

     This should build the indexes in the right tablespace AP_ITS 7) Don't forget to alter AP back to have default of AP_DTS

      ALTER USER AP DEFAULT TABLESPACE AP_DTS; The above steps should work for you. Remember, TAKE A GOOD BACKUP BEFORE YOU START THIS.
The reason you have the problem is 2 fold: 1) The schema that owns the objects (I used AP in my example) should not have QUOTA on the SYSTEM tablespace,
2) The user (AP ) was created without specifying DEFAULT and TEMPORARY tablespaces. The default is to assign the SYSTEM tablespace. Both the above are NO-NO's.

Hope this helps.

Timothy J. Stevens <tstevens_at_mail.airmail.net> wrote in article <331B8C12F509FF49.CF3F2311233C3C28.1F28B7DA4AC916DC_at_library-proxy.airnews.ne t>...
> I was recently asked how to rebuild a database where all of the
> tables had been created in the SYSTEM tablespace. The only scheme I
> could think of was to use the COPY command to create the tables in
> another database in separate tablespaces, drop the old database and
> then rename the new database back to the original name. Does any have
> any experience with this problem and will my little scheme work.
> Alternatively, is there a better way to do this
>
> Thanks!
>
>
  Received on Mon Aug 11 1997 - 00:00:00 CDT