Re: Moving a user out of the SYSTEM tablespace

From: Dale Cooper <cooper_at_beno.CSS.GOV>
Date: 24 Jun 92 14:28:42 GMT
Message-ID: <50888_at_seismo.CSS.GOV>

In article <18216_at_cmutual.com.au>, aaj_at_cmutual.com.au (Tony Jambu) writes: TJ>
In article <50886_at_seismo.CSS.GOV>, cooper_at_beno.CSS.GOV (Dale Cooper) writes:
DC>In article <1992Jun23.053320.27007_at_ml.csiro.au> peter_at_ml.csiro.au (Peter

Campbell) writes:PC>

PC>I am trying to move a user out of the SYSTEM tablespace by exporting,
PC>revoking RESOURCE privilege to SYSTEM, dropping everything, changing 
PC>the default tablespace, and importing...

DC>Ah, the joys of imp/exp!

DC>To get right to the point, IMP ignores what is currently defined as the 
DC>user's default tablespace when and if the same tablespace name is 
DC>encountered upon import (Utilities User's Guide pg 2-2).  In other words, 
DC>if you EXPorted a user' objects and those objects all resided in the SYSTEM 
DC>tablespace, IMP will attempt to reload those objects into the SYSTEM 
DC>tablespace first - regardless of the default tablespace definition!


TJ>Dale is wrong here!!!.  Oracle will import into the same tablespace as 
TJ>defined in the export IF AND ONLY IF
TJ>   (1) the original tablespace exist and
TJ>   (2) the user has resource in the original tablespace.
TJ>
TJ>If you revoke resource from the user from the original tablespace, the import

TJ>WILL NOT import the table back into the old tablespace. it will attempt to TJ>import the table into the users default tablespace.

First of all, thanks for the kind words. ;)

Obviously, I made an incorrect assumption. Anyhow, the subtle key to the issue at hand is permissions. If you (the DBA) have granted global connect and resource to the user (my assumption and a big time brain fart at that) then you WILL encounter the problems being addressed. But as you have explained in your reply, the PROPER solution does indeed depend heavily on how you allow users to use the database. The key being resource privileges granted to certain tablespaces as opposed to global resource privileges. Thanks for sheading light on this point.

TJ>Now, if you have not changed the default TS, it will fail to import.  But if
TJ>you have already alterd the user's default TS to be someother TS, it will
TJ>create the table in the new default TS (if you have granted resource).

Dale Cooper, DBA
Center for Seismic Studies
Arlington, VA Received on Wed Jun 24 1992 - 16:28:42 CEST