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

In article <917600702.25276.0.nnrp-07.9e984b29_at_news.demon.co.uk>, jonathan_at_jlcomp.demon.co.uk (Jonathan Lewis) wrote:

> 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
> >
>
>
>
>

For the indexes you can use the 'alter index rebuild' command to put it into another tablespace - I was surprised to find out that it's clever enough to use the storage parameters from the existing index too .... shame there's not an equivalent for tables!

Dave Lane (dlane_at_pt.lu) Received on Wed Feb 03 1999 - 16:03:27 CST