Re: Moving objects to a different tablespace using imp/exp

Your problem is that SCOTT (in your case) is allowed to write to the SYSTEM tablespace. The export file contains the tablespace information for the objects exported, so naturally imports the objects back to the same place - IF IT CAN.

The solution, which is sometimes a bit of a nuisance if you want to do this for a DBA type account is:

    export the objects
    drop the old objects
    select a target tablespace, make this the default for SCOTT     make sure that SCOTT cannot use space in the SYSTEM tablespace     import the objects

If you want to separate tables and indexes, you will have to fiddle about a bit more - import the first time without indexes, then change SCOTT's default tablespace to the tablespace for indexes, then import the second time with ROWS=N, IGNORE=Y. Even then any primary/unique key constraints will have dumped their indexes into the first tablespace.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

.
Steve wrote in message ...
>I am trying to move objects owned by SCOTT out of the SYSTEM tablespace to
>another tablespace (USER) using imp/exp but the objects keep getting
created
in
>the SYSTEM tablespace.
>I did a imp scott/tiger show=y and all the create table statements have
>"TABLESPACE SYSTEM" at the end.
>How do I move SCOTT's objects (and other non-SYSTEM objects put in there by
>inadvertently) out of the SYSTEM ?
>Thanks for your help
>Steve
>
Received on Fri Jan 29 1999 - 03:05:22 CST