SYSAUX tablespace is growing

[Former Member]

Dear Friends,

I have a problem with SYSAUX tablespace, which is drastically growing.

We have netweaver7.0 (stand alone j2ee) with Oracle 10.2.0.

Last week I have extended the tablespace with 2GB, but this week it consumes 500 MB out of 2GB.

I have checked below queries-

SELECT occupant_name,occupant_desc,space_usage_kbytes FROM

v$sysaux_occupants;

select tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments

where tablespace_name='SYSAUX'

group by tablespace_name,;

Please let me know where may be the problem.

Thanks,

Sudip

[Former Member · Accepted solution]

Like so often AWR is responsible for the highest amount of AWR space. Taking into account the significant benefits of AWR history data I would not change anything here. The consumed space is basically related to the AWR history retention (default: 7 days, SAP recommendation: 42 days) and in some systems AWR allocates several GB. So currently I don't think that you have to worry.

[Former Member · Accepted solution]

Dear Friend,

Still now I did not get any solution for my problem.

What could be the reason? My quality system's hardware resource as well as database configuration same.

But SYSAUX in quality system is having no problem.

Thanks.

Sudip Saha

[stefan_koehler]

Hello Sudip,

and what did the two queries return (especially the first one)?

Regards

Stefan

[Former Member]

1

• select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants

SQL> /

OCCUPANT_NAME SCHEMA_NAME SPACE_USAGE_KBYTES

LOGMNR SYSTEM 6080

LOGSTDBY SYSTEM 896

STREAMS SYS 512

XDB XDB 0

AO SYS 768

XSOQHIST SYS 768

XSAMD OLAPSYS 0

SM/AWR SYS 724672

SM/ADVISOR SYS 164608

SM/OPTSTAT SYS 17792

SM/OTHER SYS 4864

STATSPACK PERFSTAT 0

ODM DMSYS 0

SDO MDSYS 0

WM WMSYS 0

ORDIM ORDSYS 0

ORDIM/PLUGINS ORDPLUGINS 0

ORDIM/SQLMM SI_INFORMTN_SCHEMA 0

EM SYSMAN 0

TEXT CTXSYS 0

ULTRASEARCH WKSYS 0

ULTRASEARCH_DEMO_USER WK_TEST 0

EXPRESSION_FILTER EXFSYS 0

EM_MONITORING_USER DBSNMP 1600

TSM TSMSYS 256

JOB_SCHEDULER SYS 384

26 rows selected.

SQL> select tablespace_name, sum(bytes)/1024/1024/1024 "Bytes" from dba_segments group by tablespace_name;

TABLESPACE_NAME Bytes

-

---

-

---

SYSAUX .887145996

PSAPUNDO .24420166

PSAPSR3DB 3.88806152

SYSTEM .225952148

Thanks,

Sudip

[stefan_koehler]

Hello Sudip,

please put this content in the code tag .. it is absolutely unreadable.

Regards

Stefan

[Former Member]

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants;

LOGMNR - SYSTEM - 6080

LOGSTDBY - SYSTEM - 896

STREAMS - SYS - 512

XDB - XDB - 0

AO - SYS - 768

XSOQHIST - SYS - 768

XSAMD - OLAPSYS - 0

SM/AWR - SYS - 724672

SM/ADVISOR - SYS - 164608

SM/OPTSTAT - SYS - 17792

SM/OTHER - SYS - 4864

STATSPACK - PERFSTAT - 0

ODM - DMSYS - 0

SDO - MDSYS - 0

WM - WMSYS - 0

ORDIM - ORDSYS - 0

ORDIM/PLUGINS - ORDPLUGINS - 0

ORDIM/SQLMM - SI_INFORMTN_SCHEMA - 0

EM - SYSMAN - 0

TEXT - CTXSYS - 0

ULTRASEARCH - WKSYS - 0

ULTRASEARCH_DEMO_USER - WK_TEST - 0

EXPRESSION_FILTER - EXFSYS - 0

EM_MONITORING_USER - DBSNMP - 1600

TSM - TSMSYS - 256

JOB_SCHEDULER - SYS - 384

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments greoup by tablespace_name;

TABLESPACE_NAME - Bytes

SYSAUX - 0.887145996

PSAPUNDO - 0.24420166

PSAPSR3DB - 3.88806152

SYSTEM - 0.225952148

Thanks,

Sudip