System tablespace growing

From: Nate Jones <nathan.jones_at_bi-tech.com>
Date: 28 Apr 2003 11:49:42 -0700
Message-ID: <cde4fecc.0304281049.4b920a5@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:

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(*)

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 Received on Mon Apr 28 2003 - 13:49:42 CDT