This content has been marked as final. Show 8 replies
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;
Edited by: Anand... on Mar 4, 2009 7:54 PM
For what purposes are u using these tablespaces...for ex: AWR statistics reports reside on SYSAUX tablespace which might eat up some space...
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
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;
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;
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?
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
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.
The owner is MDSYS.