Re: Moving a user out of the SYSTEM tablespace

In article <4580_at_sumax.seattleu.edu> bwalker_at_sumax.seattleu.edu (Brian T. Walker) writes:
>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.
>
>

You are correct. But I just recently did a lot of this, and I noticed a problem: It seems that if you had previously granted resource *on a particular tablespace* to a user, "revoke resource from user" didn't always work. After revoking resource from the user, I could connect as that user and *still* create tables! I had to do "revoke resource on <tablespace name> from user" for all the tablespaces before the rdbms would finally believe me that I really, really wanted the user to have no resource! Then I could grant resource to the user just on the non-SYSTEM tablespace that I wanted.

In general, I think it is a very good idea to keep as little as possible in SYSTEM. Why? Because if you ever add a file to the SYSTEM tablespace, you can never remove it, unless you want to rebuild the database. On the other hand, if you have a separate tablespace for different logical uses (e.g., one for development, one for the ORACLE*CASE tables, one for user tables, one for rollback segments, etc.) you can easily drop and recreate one of these tablespaces in order to make it smaller. Thus it is easy to recover any disk space given over to Oracle should you need to do so.

-- 
Darius S. Naqvi              mail at work: darius_at_edm.isac.ca
ISA Corp.                    mail at home: darius%naqvi.uucp_at_isac.ca
Edmonton, Alberta, Canada           phone: (403) 420-8081