Re: SYSTEM schema in SYSTEM tablespace

Depending on what you are doing you may find yourself running scripts in RDBMS\ADMIN as SYSTEM and putting things into the SYSTEM tablespace that you don't have in your "toy".

Daniel Morgan

Mladen Gogala wrote:

> 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 - 05:17:35 CST