8 Replies Latest reply: Mar 4, 2009 10:33 AM by 640612 RSS

    SYSTEM and SYSAUX tablespace full

    640612
      Hello,

      Am on windows xp and I am using oracle XE. Since yesterday I realized that the tablespaces SYSTEM and SYSAUX are at 97% and 95% and I have never put anything inside. I tried resizing them datafiles but I got an error stating space cannot be added. I know the space limit on XE is 4GB. What do you suggest I should do?

      Thanks in advance.
        • 1. Re: SYSTEM and SYSAUX tablespace full
          Anand...
          Hi..

          The datafiles of these 2 tablespaces are in AUTIEXTEND ON mode by default.You can check this by querying

          select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE from dba_data_files where tablespace_name=UPPER('&tbs_name')

          To know who and what object is occupying the space:-

          select owner,segment_name,segment_owner,(bytes/1024/1024)MB,tablespace_name from dba_segments where tablespace_name='SYSTEM';

          and then run the same for SYSAUX tablespace changing the tablespace_name

          For SYSAUX tablespace:--

          select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;


          Anand

          Edited by: Anand... on Mar 4, 2009 7:54 PM
          • 2. Re: SYSTEM and SYSAUX tablespace full
            602725
            Hi,
            For what purposes are u using these tablespaces...for ex: AWR statistics reports reside on SYSAUX tablespace which might eat up some space...
            • 3. Re: SYSTEM and SYSAUX tablespace full
              640612
              I have never used those 2 TS. for anything. I ran the scripts suggested by I don't see any weird segment so now am a little bit confused because I don't know which one to drop
              • 4. Re: SYSTEM and SYSAUX tablespace full
                Anand...
                Can you post the output of the following queries.

                1. select owner,segment_name,segment_type,(bytes/1024/1024)MB,tablespace_name from dba_segments where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS')


                2. select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;



                Anand
                • 5. Re: SYSTEM and SYSAUX tablespace full
                  user482717
                  I am having the same problem with system and sysaux getting full.
                  the owner that is using system segment are SYS, SYSTEM AND OUTLN

                  SQL> SELECT OWNER, SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE TABLESPACE_NAME= 'SYSTEM' GROUP BY OWNER;

                  OWNER SUM(BYTES)/1024/1024
                  ---------- --------------------
                  OUTLN .375
                  SYS 449.375
                  SYSTEM 15.8125


                  I also have snaptshot from oct. 2008 to now. i am thinking to delete some. if i delete the old snapshot, will i free some space from sysaux?

                  Thanks.
                  • 6. Re: SYSTEM and SYSAUX tablespace full
                    640612
                    the message exceeds the maximum length of 30000 characters- Actually I can't format the ouput so that it fits on one page, and I can't see the attachment option here
                    • 7. Re: SYSTEM and SYSAUX tablespace full
                      Anand...
                      With * owner not in ('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS')* option you have so many tables in the SYSTEM tablespace.Who is the owner of the tables and indexs.

                      Anand
                      • 8. Re: SYSTEM and SYSAUX tablespace full
                        640612
                        The owner is MDSYS.