Re: System tablespace growing

what's wrong with just

select * from (select owner,segment_name,bytes/1024/1024 MB from dba_segments where TABLESPACE_NAME = 'SYSTEM' ) where rownum < 11 order by MB desc;

This gives you the top 10 space hoggers in SYSTEM

for example on my dev 9.2

this gives

• ---------- SYS OBJ$ 3.53125 SYS VIEW$ 2.359375 SYS SYN$ .6796875 SYS CON$ .171875 SYS BOOTSTRAP$ .078125 SYS FILE$ .015625 SYS UGROUP$ .015625 SYS PROXY_ROLE_DATA$ .015625 SYS PROXY_DATA$ .015625 SYS UNDO$ .015625 "Nate Jones" <nathan.jones_at_bi-tech.com> wrote in message news:cde4fecc.0304281049.4b920a5_at_posting.google.com...
  > Hi,
  >
  > I am running an Oracle 8.1.7.4 database on HP/UX, and am experiencing
  > an issue where the system tablespace has been growing quickly. It is
  > 456 Megs right now and has grown about ~100 Megs in the last 2-3
  > weeks. The *problem* is that I am wondering what is filling it up and
  > will this continue indefinately. My experience with the system
  > tablespace was that it was fairly static, but that doesnt seem to be
  > the case here
  >
  > I have searched for previous threads on this and found quite a few.
  > Howard had some good suggestions, but none of them panned out for me.
  > I have tried:
  >
  > 1. Make sure there are no tables being created in the system
  > tablespace that are "user" owned. I ran:
  >
  > select table_name from dba_tables where tablespace_name='SYSTEM'
  > and owner <> 'SYS';
  >
  > and it came back with:
  >
  > SQL> select table_name from dba_tables where tablespace_name='SYSTEM'
  > 2 and owner <> 'SYS';
  > \
  > TABLE_NAME
  > ------------------------------
  > OL$
  > OL$HINTS
  > AQ$_QUEUE_TABLES
  > AQ$_QUEUES
  > AQ$_SCHEDULES
  > DEF$_AQCALL
  > DEF$_AQERROR
  > DEF$_ERROR
  > DEF$_DESTINATION
  > DEF$_CALLDEST
  > DEF$_DEFAULTDEST
  >
  > TABLE_NAME
  > ------------------------------
  > DEF$_LOB
  > DEF$_TEMP$LOB
  > DEF$_PROPAGATOR
  > DEF$_ORIGIN
  > DEF$_PUSHED_TRANSACTIONS
  > SQLPLUS_PRODUCT_PROFILE
  > HELP
  >
  > 18 rows selected.
  >
  > I believe all are ok. (Let me know if I'm wrong there.)
  >
  >
  > 2. Did:
  >
  > SQL> select username from dba_users where default_tablespace='SYSTEM';
  >
  > USERNAME
  > ------------------------------
  > SYS
  > OUTLN
  > TRACESVR
  > DBSNMP
  >
  > SQL> select username from dba_users where
  > temporary_tablespace='SYSTEM';
  >
  > USERNAME
  > ------------------------------
  > OUTLN
  > TRACESVR
  > DBSNMP
  >
  >
  > 3. "Do a select * from dba_data_files and
  > make sure the "AUT" column is set to "Y"." (for the System datafile)
  >
  > It is.
  >
  > 4. "Also, make sure you are not using the dreadful auditing feature
  > (show
  > parameter audit_trail. If it's set to anything other than NONE, you
  > are). "
  >
  > I believe that doing a select count(*) from aud$ will tell you whether
  > or not auditing is turned on:
  >
  > SQL> select count(*) from sys.aud$;
  >
  > COUNT(*)
  > ----------
  > 0
  >
  >
  > If anyone has any other suggestions/comments, please let me know. Or
  > am I worrying over nothing (only if it will stop growing!)?
  >
  > Thanks in advance,
  >
  > Nate