Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Moving user objects out of SYSTEM

Re: Moving user objects out of SYSTEM

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Mon, 26 Apr 1999 18:05:52 -0400
Message-ID: <3724E340.7C3C9DC9@bigfoot.com>


I did end up doing this last week and it was a piece of cake. All I had to do was drop the user cascade, and then recreate it with a default tablespace other than system, and re-import. But something is bugging me now. I know I've seen situations where this sort of technique didn't work, and I got a flood of errors with TABLESPACE whatever doesn't exist. Is there some circumstance where you must do the index file schenanigans?

Jurij Modic wrote:

> On Tue, 20 Apr 1999 22:35:41 GMT, ddf_dba_at_my-dejanews.com wrote:
>
> >A full export will preserve the locations of the user objects, including the
> >tablespace, so an inport with 0 quota will not relocate the objects.
>
> Sure it will relocate the objects! Import will try to create object in
> the same tablespaces as the objects were in originally. But if
> tablespace with such a name does not exist or if the schema owner has
> no quota in it, then imp will create objects in the user's default
> tablespace.
>
> >You will need to generate a script from the import for the specific user's
> >objects, then modify that script to point the objects to the desired
> >tablespace.
>
> There is no need to go through this "import to indexfile, then edit it
> etc..." hasle if you only need all imported objects to be in the same
> tablespace, different from the original one. Removing a quota from the
> original tablespace will relocate the segments to user's default
> tablespace. However, if you want to separate different object types in
> different tablespace (for example, separating tables from indexes) or
> if you want to manualy resize segments/extents, then your method would
> be reasonable.
>
> Jurij Modic <jmodic_at_src.si>
> Certified Oracle7 DBA (OCP)
> ================================================
> The above opinions are mine and do not represent
> any official standpoints of my employer
Received on Mon Apr 26 1999 - 17:05:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US