Starting with Oracle 10g , Oracle introduced SYSAUX tablespace, where it maintains the information about components that are not MUST for a database to be up and running .
If SYSAUX is not available , core functionality is not impacted , but AWR , ADDM , SQL plan management , Diagnostic Pack , ... .( to name a few ) will be impacted.
Oracle provides a script to estimate the size of the SYSAUX tablespace. This script is located under ORACLE_HOME/admin.
The script interview you with series of questions and comes up with the report.
Here is an example.
Comments welcome...!
c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 22 16:41:23 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 22 16:41:23 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @utlsyxsz
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
Enter value for report_name:
...
....
c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>type utlsyxsz.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Estimated at
16:29:00 on Sep 22, 2011 ( Thursday ) in Timezone -04:00
DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
----------- ---------------------------------------- ----- ----------------- ---
* DBDEV COR-DB02 - Microsoft Windows x86 64-bi 1 12:14:51 (09/16) NO
t
~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 1,173.8 MB
|
| Total size of SM/AWR 254.9 MB ( 21.7% of SYSAUX )
| Total size of SM/OPTSTAT 264.4 MB ( 22.5% of SYSAUX )
| Total size of XDB 127.8 MB ( 10.9% of SYSAUX )
| Total size of SM/ADVISOR 106.6 MB ( 9.1% of SYSAUX )
| Total size of EM 96.6 MB ( 8.2% of SYSAUX )
| Total size of SDO 45.3 MB ( 3.9% of SYSAUX )
| Total size of AO 41.9 MB ( 3.6% of SYSAUX )
| Total size of XSOQHIST 41.9 MB ( 3.6% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE 28.6 MB ( 2.4% of SYSAUX )
| Total size of ORDIM/ORDDATA 13.6 MB ( 1.2% of SYSAUX )
| Total size of LOGMNR 12.3 MB ( 1.0% of SYSAUX )
| Total size of SM/OTHER 6.4 MB ( 0.5% of SYSAUX )
| Total size of SMON_SCN_TIME 6.3 MB ( 0.5% of SYSAUX )
| Total size of JOB_SCHEDULER 6.2 MB ( 0.5% of SYSAUX )
| Total size of PL/SCOPE 5.9 MB ( 0.5% of SYSAUX )
| Total size of XSAMD 5.1 MB ( 0.4% of SYSAUX )
| Total size of EXPRESSION_FILTER 3.6 MB ( 0.3% of SYSAUX )
| Total size of TEXT 3.6 MB ( 0.3% of SYSAUX )
| Total size of WM 2.0 MB ( 0.2% of SYSAUX )
| Total size of EM_MONITORING_USER 1.7 MB ( 0.1% of SYSAUX )
| Total size of LOGSTDBY 1.4 MB ( 0.1% of SYSAUX )
| Total size of STREAMS 1.0 MB ( 0.1% of SYSAUX )
| Total size of ORDIM 0.4 MB ( 0.0% of SYSAUX )
| Total size of AUTO_TASK 0.3 MB ( 0.0% of SYSAUX )
| Total size of Others 95.9 MB ( 8.2% 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
| otherwise enter an alternative
|
Enter value for interval:
** Value for 'Interval Setting': 60
|
| For 'Retention Setting',
| Press
| otherwise enter an alternative
|
Enter value for retention: 30
** Value for 'Retention Setting': 30
|
| For 'Number of Instances',
| Press
| otherwise enter an alternative
|
Enter value for num_instances: 1
** Value for 'Number of Instances': 1
|
| For 'Average Number of Active Sessions',
| Press
| otherwise enter an alternative
|
Enter value for active_sessions: 50
** Value for 'Average Number of Active Sessions': 50
| ***************************************************
| Estimated size of AWR: 3,558.0 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 30.00 days
| Num Instances - 1
| Active Sessions - 50.00
| Datafiles - 6
| ***************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
| otherwise enter an alternative
|
Enter value for number_of_tables: 2000
** Value for 'Number of Tables': 2000
|
| For 'Number of Partitions',
| Press
| otherwise enter an alternative
|
Enter value for number_of_partitions: 2500
** Value for 'Number of Partitions': 2500
|
| For 'Statistics Retention',
| Press
| otherwise enter an alternative
|
Enter value for stats_retention:
** Value for 'Statistics Retention': 31
|
| For 'DML Activity',
| Press
| otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity: 2
** Value for 'DML Activity': 2
| ***************************************************
| Estimated size of Stats history 146.7 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 2,000
| Indexes - 2,758
| Columns - 32,097
| Partitions - 2,500
| Indexes on Partitions - 684
| Columns in Partitions - 65,142
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************
~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 3,558.0 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 30.00 days
| Num Instances - 1
| Active Sessions - 50.00
| Datafiles - 6
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 146.7 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 2,000
| Indexes - 2,758
| Columns - 32,097
| Partitions - 2,500
| Indexes on Partitions - 684
| Columns in Partitions - 65,142
| 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 XDB 127.8 MB
| Est size of SM/ADVISOR 106.6 MB
| Est size of EM 96.6 MB
| Est size of SDO 45.3 MB
| Est size of AO 41.9 MB
| Est size of XSOQHIST 41.9 MB
| Est size of SQL_MANAGEMENT_BASE 28.6 MB
| Est size of ORDIM/ORDDATA 13.6 MB
| Est size of LOGMNR 12.3 MB
| Est size of SM/OTHER 6.4 MB
| Est size of SMON_SCN_TIME 6.3 MB
| Est size of JOB_SCHEDULER 6.2 MB
| Est size of PL/SCOPE 5.9 MB
| Est size of XSAMD 5.1 MB
| Est size of EXPRESSION_FILTER 3.6 MB
| Est size of TEXT 3.6 MB
| Est size of WM 2.0 MB
| Est size of EM_MONITORING_USER 1.7 MB
| Est size of LOGSTDBY 1.4 MB
| Est size of STREAMS 1.0 MB
| Est size of ORDIM 0.4 MB
| Est size of AUTO_TASK 0.3 MB
| Est size of Others 95.9 MB
| Est size of SM/AWR 3,558.0 MB
| Est size of SM/OPTSTAT 146.7 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 4,359.1 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************
End of Report
c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>
