Re: Moving user objects out of SYSTEM

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 - 17:35:41 CDT