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: Tue, 20 Apr 1999 23:41:32 -0400
Message-ID: <371D48EC.D5C2F454@bigfoot.com>


Thanks to both of you...I'll be doing this off hours Thursday night.

ddf_dba_at_my-dejanews.com wrote:

> In article <371C9411.79D1F8EA_at_bigfoot.com>,
> Doug Cowles <dcowles_at_bigfoot.com> wrote:
> > Can someone outline a procedure to move a user's objects
> > out of SYSTEM? If I have a full export for example, and then
> > re-import just one user with a 0 quota on system, will that do it?
> > Is there an easier way?
> >
> > - Dc.
> >
> >
>
> 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. 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.
>
> Generating a script from an export is easy:
>
> imp <user/pass> fromuser=<user> file=<filename> indexfile=<indexfilename>
>
> This will create an indexfile, a SQL*Plus script containing the table and
> index definitions for the specific user. The table definitions will be REM'd
> out, so you will need to remove the REM statements. Your next task is to
> edit the tablespace parameters to point to the desired tablespace or
> tablespaces. Remove the CONNECT statement, and all lines containing the word
> "rows", and you will have a script to create the tables and indexes for the
> user. Run this script as the user, to create the empty objects in the
> database. Next, import the entire file, ignoring the create error if the
> object exists. This will relocate the objects, and populate them with data.
> As for sequences and views, you will need to query dba_source, spool the
> output to a file and edit the resulting file:
>
> break on name on type skip 1
> select name, type, text
> from dba_source
> where owner = '<user>'
> order by type, line;
>
> This will generate a script of all objects for the user in the database,
> allowing you to relocate every object the user has created. I only list the
> first method since you have a full export and the script is fairly complete.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Apr 20 1999 - 22:41:32 CDT

Original text of this message

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