Re: Using COPY to rebuilding database.

Just changing the default tablespace of the user doesn't ensure the import to go into that tablespace. Setting quota to 0 on system tablespace is also unlikely to work as Import tries to create tables in the tablespace from where they were exported.

To make this scheme work first import into indexfile which will give script for creating all the objects. (you can see they all point to system tablespace). Everyline in the indexfile will be commented except create index commands. Now you can edit the file changing names of tablespaces to whatever you want it to be and don't forget to uncomment the lines.

Then drop all the objects of that user. Then run the index file to precreate all the objects in correct tablespaces.

Now run the actual import with ignore=y option.

For the whole thing to work user must have quota in the respective tablespaces.

Sugu

In article <01bca672$30fb7640$9198b8cd_at_newmicronpc>,   "diversif" <xnospamx.huber_at_divers-inc.com> wrote:
>
> 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!
> >
> >

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet