What is going on inside your SYSAUX tablespace .

size in SYSAUX tablespace
Oracle SYSAUX tablespace


@$ORACLE_HOME/rdbms/admin/utlsyxsz.sql


This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: space.txt

Using the report name space.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
14:24:41 on Sep 19, 2011 ( Monday ) in Timezone +01:00


DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
----------- ---------------------------------------- ----- ----------------- ---
* ORCL apt-lnxtst-01 - Linux IA (32-bit) 1 09:45:16 (09/19) NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 429.3 MB
|
| Total size of SM/AWR 51.9 MB ( 12.1% of SYSAUX )
| Total size of SM/OPTSTAT 83.1 MB ( 19.4% of SYSAUX )
| Total size of EM 70.1 MB ( 16.3% of SYSAUX )
| Total size of XDB 48.3 MB ( 11.2% of SYSAUX )
| Total size of SDO 40.9 MB ( 9.5% of SYSAUX )
| Total size of TEXT 32.6 MB ( 7.6% of SYSAUX )
| Total size of AO 23.9 MB ( 5.6% of SYSAUX )
| Total size of XSOQHIST 23.9 MB ( 5.6% of SYSAUX )
| Total size of SM/OTHER 17.5 MB ( 4.1% of SYSAUX )
| Total size of XSAMD 15.6 MB ( 3.6% of SYSAUX )
| Total size of SM/ADVISOR 10.6 MB ( 2.5% of SYSAUX )
| Total size of WM 7.1 MB ( 1.7% of SYSAUX )
| Total size of LOGMNR 5.9 MB ( 1.4% of SYSAUX )
| Total size of EXPRESSION_FILTER 3.6 MB ( 0.8% of SYSAUX )
| Total size of EM_MONITORING_USER 1.6 MB ( 0.4% of SYSAUX )
| Total size of LOGSTDBY 0.9 MB ( 0.2% of SYSAUX )
| Total size of JOB_SCHEDULER 0.8 MB ( 0.2% of SYSAUX )
| Total size of STREAMS 0.5 MB ( 0.1% of SYSAUX )
| Total size of ORDIM 0.5 MB ( 0.1% of SYSAUX )
| Total size of ODM 0.3 MB ( 0.1% of SYSAUX )
| Total size of TSM 0.3 MB ( 0.1% of SYSAUX )
| Total size of Others -10.3 MB ( -2.4% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
| - Interval Setting (minutes)
| - Retention Setting (days)
| - Number of Instances
| - Average Number of Active Sessions
| - Number of Datafiles

|
| For 'Interval Setting',
| Press to use the current value: 60.0 minutes
| otherwise enter an alternative
|
Enter value for interval:

** Value for 'Interval Setting': 60

|
| For 'Retention Setting',
| Press to use the current value: 7.00 days
| otherwise enter an alternative
|
Enter value for retention:

** Value for 'Retention Setting': 7

|
| For 'Number of Instances',
| Press to use the current value: 1.00
| otherwise enter an alternative
|
Enter value for num_instances:

** Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
| Press to use the current value: 24.0
| otherwise enter an alternative
|
Enter value for active_sessions:

** Value for 'Average Number of Active Sessions': 24

| ***************************************************
| Estimated size of AWR: 440.3 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 7.00 days
| Num Instances - 1
| Active Sessions - 24.00
| Datafiles - 9
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
| - Number of Tables in the Database
| - Number of Partitions in the Database
| - Statistics Retention Period (days)
| - DML Activity in the Database (level)

|
| For 'Number of Tables',
| Press to use the current value: 600.0
| otherwise enter an alternative
|
Enter value for number_of_tables:

** Value for 'Number of Tables': 600

|
| For 'Number of Partitions',
| Press to use the current value: 0.00
| otherwise enter an alternative
|
Enter value for number_of_partitions:

** Value for 'Number of Partitions': 0

|
| For 'Statistics Retention',
| Press to use the current value: 31.0 days
| otherwise enter an alternative
|
Enter value for stats_retention:

** Value for 'Statistics Retention': 31

|
| For 'DML Activity',
| Press to use the current value: 2
| otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity:

** Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history 30.4 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 600
| Indexes - 1,006
| Columns - 4,507
| Partitions - 0
| Indexes on Partitions - 0
| Columns in Partitions - 0
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 440.3 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 7.00 days
| Num Instances - 1
| Active Sessions - 24.00
| Datafiles - 9
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 30.4 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 600
| Indexes - 1,006
| Columns - 4,507
| Partitions - 0
| Indexes on Partitions - 0
| Columns in Partitions - 0
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| For all the other components, the estimate
| is equal to the current space usage of
| the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************

| Est size of EM 70.1 MB
| Est size of XDB 48.3 MB
| Est size of SDO 40.9 MB
| Est size of TEXT 32.6 MB
| Est size of AO 23.9 MB
| Est size of XSOQHIST 23.9 MB
| Est size of SM/OTHER 17.5 MB
| Est size of XSAMD 15.6 MB
| Est size of SM/ADVISOR 10.6 MB
| Est size of WM 7.1 MB
| Est size of LOGMNR 5.9 MB
| Est size of EXPRESSION_FILTER 3.6 MB
| Est size of EM_MONITORING_USER 1.6 MB
| Est size of LOGSTDBY 0.9 MB
| Est size of JOB_SCHEDULER 0.8 MB
| Est size of STREAMS 0.5 MB
| Est size of ORDIM 0.5 MB
| Est size of ODM 0.3 MB
| Est size of TSM 0.3 MB
| Est size of Others -10.3 MB

| Est size of SM/AWR 440.3 MB
| Est size of SM/OPTSTAT 30.4 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 764.9 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************