Re: Moving user objects out of SYSTEM

Doug,

The full export file contains all of the necessary information to restore the objects to their original locations, no matter what the default tablespace is for the user in question. To use the import you will need to create an indexfile:

imp <user/pass> file=<filename> fromuser=<user> indexfile=<indexfilename>

Edit the indexfile you've created, removing the REM statements, any CONNECT statements and remarked lines containing the word "rows" and changing the tablespace specifications for the tables and indexes to the tablespace or tablespaces in which the objects should reside. Run this script as <user>, creating the objects where you want them. You may then proceed with the import, choosing to ignore the create error because these objects exist. Simply importing the file into the current database will not relocate the objects, even with a quota of 0, since tablespaces are explicitly stated in the stored create statements.

Good luck!!!

In article <371CA611.D4A88CB7_at_us.oracle.com>,   Pete Sharman <psharman_at_us.oracle.com> wrote:
> This is a multi-part message in MIME format.
> --------------65106186FD85B05545C04B48
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Doug
>
> If you already have the full export, fine, you can use it. But if you
> only want to do this for one user, maybe a user level export would be
> better. Some things to check:
>
> 1. Ensure the user doesn't have the UNLIMITED TABLESPACE privilege, or
> the RESOURCE role (which has this privilege). If they do, they can
> bypass the quota anyway.
>
> 2. Ensure the default tablespace for the user is something other than
> SYSTEM.
>
> Then you should be able to import the user (FROMUSER/TOUSER with a full
> export, or just imp user_being_imported_username/password).
>
> HTH.
>
> Pete
>
> Doug Cowles 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.
>
> --
>
> Regards
>
> Pete
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Peter Sharman Email: psharman_at_us.oracle.com
> WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
>
> Worldwide Internal Services Education (650)607 0109 (local)
> San Francisco
>
> SQL> select standard_disclaimer, witty_remark
> 2 from company_requirements;
>
> Opinions are mine and do not necessarily reflect those of Oracle
> Corporation
>
> "Controlling application developers is like herding cats."
> Kevin Loney, ORACLE DBA Handbook
> "Oh no it's not! It's much harder than that!"
> Bruce Pihlamae, long term ORACLE DBA
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> --------------65106186FD85B05545C04B48
> Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Pete Sharman
> Content-Disposition: attachment; filename="vcard.vcf"
>
> begin: vcard
> fn: Pete Sharman
> n: Sharman;Pete
> org: Oracle Corporation
> adr: 500 Oracle Parkway M/S OPL-B1024;;;Redwood
Shores;California;94065;USA
> email;internet: psharman_at_us.oracle.com
> title: Courseware Development Manager
> tel;work: +1.650.607.0109
> tel;fax: +1.650.633.1669
> x-mozilla-cpt: ;0
> x-mozilla-html: FALSE
> version: 2.1
> end: vcard
>
> --------------65106186FD85B05545C04B48--
>
>

-----------== 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:42:58 CDT