Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Large System Tablespace ???
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