Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Large System Tablespace ???

Re: Large System Tablespace ???

From: Mike Hately <mike.hately_at_virgin.net>
Date: 2000/05/26
Message-ID: <8gkcg6$kp6$1@lure.pipex.net>#1/1

You need to check the following :

Is the space actually being used (someone may have typed 2000M rather than 200M) :

    select sum(bytes) from dba_free_space where tablespace_name='SYSTEM;

Has anyone written permanent objects to the SYSTEM tablespace :

    select owner,segment_name,bytes/1024 K from dba_segments where owner not in ('SYS','SYSTEM');

Do any users write to SYSTEM by default :

    select username,default_tablespace,temporary_tablespace     from dba_users
    where default_tablespace='SYSTEM'

          or temporary_tablespace='SYSTEM';

(It's OK for SYS/SYSTEM to have SYSTEM as default tablespace but no-one else should have. NOBODY should have SYSTEM as their temporary tablespace).

Regards

Mike Hately

<old_flyer_at_my-deja.com> wrote in message news:8ghb6a$qqo$1_at_nnrp1.deja.com...
> ashwini <ashwini123_at_my-deja.com> wrote:
> > I'm a new DBA. I just took over a new database and notice it has a
 very
> > large system tablespace although most objects belong to a single
> schema.
>
> You don't say what Version of Oracle this is. I know that there was an
> issue with using the Database assistant in Oracle8i. I saw it create a
> 2 GB System ts on an NT host.
>
> > Auditing is not turned on, there is little fragmentation, the objects
> > seem properly sized and no objects other than the data dictionary are
 in
> > the SYSTEM tablespace. I Realizie this indicates high usage of
> > something,
>
> How much free space is in the ts? query dba_free_space where the
> tablespace_name = {tsname} and see how much of the ts is being used.
> Could be that the original layout set aside a large area for SYSTEM but
> didn't functionally use it.
>
> Another thing to check: what is the temp ts defined for the users? It
> could be that the original DBA/whatever that set up the users let the
> defaults happen on setup. If the users temp ts is set to system, then
> all their sorts are happening in the SYSTEM ts!!!! Bad stuff!!!
>
> Do I need to check a particular related init.ora parameter?
> > Which one and why?
>
> Offhand, I don't think any init.ora setting is driving this.
>
> My $.02 US.
>
> Dwight Taylor
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri May 26 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US