Re: SYSTEM schema in SYSTEM tablespace

From: Mladen Gogala <mgogala_at_earthlink.net>
Date: Thu, 24 Jan 2002 07:35:20 GMT
Message-ID: <pan.2002.01.24.02.35.21.776963.16848@earthlink.net>

On Wed, 23 Jan 2002 21:10:09 -0500, Tom Dyess wrote:

>> I have been often advised that the only schema that should own objects
>> in

> the

>> SYSTEM tablespace is SYS; that even SYSTEM should be assigned to other
>> tablespaces just like any other user.
>>
>> All of our db's still have SYSTEM assigned to the system tablespace

> (though his

>> temp tablespace has been reassigned to a temp ts).  I have discussed
>> this

> with

>> my partner and he says he sees no need to create the recommended TOOLS
>> ts

> and

>> move SYSTEM's objects to it.  I have nothing to counter except "everyone

> says to

>> do it that way."  Is there any demo/test I could do to demonstrate to
>> both

> of us

>> why this is good practice?

>
> If you fill up your SYSTEM tablespace, you are screwed. This isn't as
> tragic now that oracle as auto expansion on tablespaces, but if your
> SYSTEM tablespace won't automatically allocate more space and it is full,
> you are in trouble. Same logic that you shouldn't your complete disk the
> root partition in UNIX, ie make /var /usr and /opt a partition as well.
>
> Tom
> www.oraclepower.com

What are we talking about? Here is the list of tables (and sizes) belonging to the user 'SYSTEM'. This is a freshly installed toy database. If you don't use advanced queueing , you're just fine and peachy. As for being "screwed", that doesn't hold water any more as you can move tables around as of 8.1.7. The only table that should be moved is SYS.AUD$ and that only if you're auding your database.

  1 select segment_name,tablespace_name,sum(bytes)/1048576 "MB"   2 from dba_extents
  3 where owner='SYSTEM' and

  4	    segment_type='TABLE'
  5	    group by segment_name,tablespace_name
  6*	    order by "MB"

SQL> / SEGMENT_NAME TABLESPACE_NAME MB

------------------------- -------------------- ----------
AQ$_QUEUES		  SYSTEM		    .0625
AQ$_QUEUE_TABLES	  SYSTEM		    .0625
AQ$_SCHEDULES		  SYSTEM		    .0625
DEF$_AQCALL		  SYSTEM		    .0625
DEF$_AQERROR		  SYSTEM		    .0625
DEF$_CALLDEST		  SYSTEM		    .0625
DEF$_DEFAULTDEST	  SYSTEM		    .0625
DEF$_DESTINATION	  SYSTEM		    .0625
DEF$_ERROR		  SYSTEM		    .0625
DEF$_LOB		  SYSTEM		    .0625
DEF$_ORIGIN		  SYSTEM		    .0625

SEGMENT_NAME		  TABLESPACE_NAME	       MB
------------------------- -------------------- ----------
DEF$_PROPAGATOR 	  SYSTEM		    .0625
DEF$_PUSHED_TRANSACTIONS  SYSTEM		 0625
DEF$_TEMP$LOB		  SYSTEM		    .0625
DUMMY			  USERS 		    .0625
HELP			  SYSTEM		    .0625
PLAN_TABLE		  USERS 		    .0625
SQLPLUS_PRODUCT_PROFILE   SYSTEM		 0625

18 rows selected.

SQL>

-- 
Mladen Gogala

Received on Thu Jan 24 2002 - 01:35:20 CST