Re: System tablespace growing

nathan.jones_at_bi-tech.com (Nate Jones) 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!)?

This is "good" worrying. :-)

Try the tablespace mapping feature of more recent versions of OEM. Go into DBA Studio, log into your db with a dba account (system usually works for me), expand the storage tree, right click on the system tablespace and select the tablespace map. You can sort by largest object by clicking on the column title.

If it turns out to be something like FET$, switch to LMT's.

>
> Thanks in advance,
>
> Nate

jg

--
@home.com is bogus.
"Use of this site is subject to express terms of use, which prohibit
commercial use of this site." - Ticketmaster web site.  Guess they
don't want to do their business on their web site.