Re: SYSTEM Tablespace is FULL

Hari Om wrote:

>Thanks Daniel,
>
>I tried the following query and got following:
>SELECT DISTINCT owner
>FROM dba_segments
>WHERE tablespace_name = 'SYSTEM';
>
>OWNER
>---------------------------------------------------------
>MDSYS
>ORDSYS
>OUTLN
>SYS
>SYSTEM
>WMSYS
>
>Is that all is needed or is it more....? Do I need to reomve some of
>these....?
>
>Also, how do I check if AUDITING is turned off or on...becos it seems
>that AUDITing uses SYSTEM TS....
>
>THANKS *.* again!
>
>HARI OM
>
>
>Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<1062813947.968701_at_yasure>...
>
>
>>Hari Om wrote:
>>
>>
>>
>>>I am using Oracle 9.2.0.1 on IBM AIX 5.1L
>>>
>>>My following Tablespaces are 99% full....is it normal...? do I need to
>>>increase manually or is ti done automatically:
>>>---------------------------------------------------------
>>>TABLESPACE SIZE USED %USED
>>>---------------------------------------------------------
>>>SYSTEM 400MB 394MB 98
>>>EXAMPLE 138MB 137MB 99
>>>XDB 38MB 37MB 99
>>>---------------------------------------------------------
>>>
>>>Do you think increasing the above Tablespace will increase the speed
>>>also?
>>>
>>>Any related informaiton is appreciated.
>>>
>>>(Apologize for my ignorance)
>>>THANKS!
>>>
>>>
>>>
>>>
>>I think you have a mess. There is no way you should have 394MB in the
>>system tablespace. Most likely what you have done is put lots of stuff
>>into the SYSTEM tablespace that doesn't belong there. And you need to
>>delete all of that stuff and recreate it elsewhere.
>>
>>Try the following query:
>>
>>SELECT DISTINCT owner
>>FROM dba_segments
>>WHERE tablespace_name = 'SYSTEM';
>>
>>You shouldn't see anything there except segments created by Oracle.
>>during installation.
>>
>>I have 9.2 installed with a 250MB SYSTEM tablespace ... and 68MB is unused.
>>
>>And once again ... please stop cross-posting.
>>
>>

These schemas were installed by Oracle so unless you've been building objects in one of them you should be fine leaving them as they are. The question is still what is taking up the space because there appears to be something definitely wrong.

Try these:

select tablespace_name, sum(bytes)/1024/1024 from dba_data_files
group by tablespace_name;

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space
group by tablespace_name;

select segment_name, sum(bytes/1024/1024) SUM_COL from dba_segments
where tablespace_name = 'SYSTEM'
group by segment_name
having sum(bytes/1024/1024) > 1
order by SUM_COL DESC

and report back the results.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)