Re: Moving a user out of the SYSTEM tablespace

In article <50886_at_seismo.CSS.GOV> cooper_at_beno.CSS.GOV (Dale Cooper) writes:
>In article <1992Jun23.053320.27007_at_ml.csiro.au> peter_at_ml.csiro.au (Peter Campbell) writes:
>>I am trying to move a user out of the SYSTEM tablespace by exporting,
>>revoking RESOURCE privilege to SYSTEM, dropping everything, changing
>>the default tablespace, and importing. Unfortunately despite having no
>>quota the user is still able to create things in SYSTEM so everything is
>>being recreated there instead of being forced to the new tablespace.
>>
>>What am I doing wrong?
>>
>>Peter Campbell peter_at_ml.csiro.au
>>CSIRO Marine Labs, Hobart, Australia
>

It sounds like you are real close to getting what you want. The key here is to make sure you revoke gobal resource from the user prior to the import. Here is a sample session I used to do what you wanted:

1. export the user's objects using export user
2. change the user's resources: revoke resource from dbuser; grant resource on alternate_tblspc to dbuser; alter user dbuser default tablespace alternate_tblspc;
3. drop all of the user's objects
4. import the user's objects using import user

This should put the tables in the tablespace you want, not system. If you don't revoke global resource, import will put the objects back where they came from.

>Ah, the joys of imp/exp!
>
>To get right to the point, IMP ignores what is currently defined as the users
>default tablespace when and if the same tablespace name is encountered upon
>import (Utilities User's Guide pg 2-2). In other words, if you EXPorted a
>user' objects and those objects all resided in the SYSTEM tablespace, IMP will
>attempt to reload those objects into the SYSTEM tablespace first - regardless
>of the default tablespace definition! (This has always been a pet peve of
>mine, but that's another article entirely) If a SYSTEM tablespace does not
>exist in the target database, then and only then will IMP attempt to use the
>default tablespace definition. Of course since every Oracle database that I
>have ever had the pleasure to deal with has a SYSTEM tablespace, you can pre

[much stuff deleted]

Doing a user import, import will try to put the objects back where they came from if the tablespace exists and the user still has resource priv on the original tablespace. If not, then import will place the objects in the default tablespace (if the user has resource privs there). As long as the resources are set correctly, it doesn't matter if the original tablespace exists or not.

The catch-22 situation occurs when trying to do a full database import. In this case you are correct, import will put the objects back where they came from regardless of the defaults (this is primarily true because all of the resources, etc. are reset at the beginning of the import). In this case, if the tablespace the objects came from doesn't exist, it even recreates the tablespace 8-(, not fun if you don't have the required disk space still available.

Brian

-- 
--------------------------------------------------------------------------
| Brian Walker               |  o | o  | My comments are my own doings - |
| Boeing Computer Services   |   /     |   not my company's              |
| bwalker_at_sumax.seattleu.edu | (-----) |                                 |