SYSAUXが逼迫 標準の見積ツールで必要サイズを見積もる

sysaux表領域が逼迫したときに確認したメモ。

utlsyxsz.sqlというツールがORACLE_HOMEの下の方にあってこれを実行するとSYSAUXの見積サイズを出してくれる。

++-----
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> col schema_name for a10
SQL> col occupant_name for a25
SQL> col occupant_desc for a55
SQL> select schema_name, occupant_name, occupant_desc from v$sysaux_occupants;
SCHEMA_NAM OCCUPANT_NAME OCCUPANT_DESC
---------- ------------------------- -------------------------------------------------------
SYSTEM LOGMNR LogMiner
SYSTEM LOGSTDBY Logical Standby
SYS SMON_SCN_TIME Transaction Layer - SCN to TIME mapping
AUDSYS AUDSYS AUDSYS schema objects
～～～

SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants;
OCCUPANT_NAME SPACE_USAGE_KBYTES
------------------------- ------------------
LOGMNR 14272
LOGSTDBY 1536
SMON_SCN_TIME 4352
AUDSYS 1600
PL/SCOPE 1600
STREAMS 1024
AUDIT_TABLES 0
XDB 70144
AO 39680
XSOQHIST 39680
XSAMD 0
～～～

SQL> @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 <return> to continue, otherwise enter an alternative.
report_nameに値を入力してください: report.file
Using the report name report.file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report

～～～

| Est size of EM_MONITORING_USER 0.6 MB
| Est size of ORDIM 0.4 MB
| Est size of AUTO_TASK 0.3 MB
| Est size of Others 244.2 MB
| Est size of SM/AWR 550.5 MB
| Est size of SM/OPTSTAT 49.0 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 1,231.7 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************
End of Report

ってことでした。1.2GBもみんな使ってるの？とりあえず2GBにするけど。