Re: Moving a user out of the SYSTEM tablespace

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

Ah, the joys of imp/exp!

To get right to the point, IMP ignores what is currently defined as the user's 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 pretty much punt the IMP/EXP route.

Try as a DBA...

	SQL> alter user blahblah
	  2  default tablespace new_tablespace;

	SQL> connect blahblah/password
	Connected.
	SQL> create table baba2 as select * from baba;
	
	Table created.

	SQL> drop table baba;

	Table dropped.

	SQL> rename baba2 to baba;

For all you SQL police, I know there are other methods...that's the joy of SQL.

some key problems to consider with this method:

1. When you do the CREATE TABLE AS you ignore all indices. All indices must be recreated for the new table.
2. When you use CREATE TABLE AS you also have no control over storage parameters. You are stuck with what is defined as your default. In other words, one cannot issue:

		CREATE TABLE blahdeeblah2 as select * from blahdeeblah
		storage (initial ...) tablespace ...;

	This can be overriden by issuing a CREATE TABLE statement,
	then issuing INSERT INTO ... SELECT * FROM ...;

3)	Forget array inserts.  If you have tiny rollback segments and
	the table is quite large, you may have trouble doing this.  You
	may opt for PL/SQL to load chunks of records.

Dale Cooper, DBA
Center for Seismic Studies
Arlington, VA Received on Tue Jun 23 1992 - 15:17:30 CEST