Re: Large System Tablespace ???

  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 Wed May 24 2000 - 00:00:00 CDT