Hello all,
V. 11.2
We have the SYSAUX tblsp that is getting very large although I have reduced the retention of the snapshots to 10 days instead of 30 but would need to purge the old stats.
This is a query of the components and I noticed that the STATSPACK REPOSITORY is the largest component at 23 GB.
SELECT OCCUPANT_NAME
,OCCUPANT_DESC
,SPACE_USAGE_KBYTES / 1024 "MB Used"
FROM v$sysaux_occupants;
| STATSPACK | Statspack Repository | 23487.75 |
| AUDIT_TABLES | DB audit tables | 3904.25 |
| SM/AWR | Server Manageability - Automatic Workload Repository | 758.75 |
| TEXT | Oracle Text | 209.4375 |
| EM | Enterprise Manager Repository | 158.1875 |
| JOB_SCHEDULER | Unified Job Scheduler | 116.1875 |
| XDB | XDB | 60.625 |
| SDO | Oracle Spatial | 47.8125 |
| PL/SCOPE | PL/SQL Identifier Collection | 20 |
| SM/ADVISOR | Server Manageability - Advisor Framework | 15.8125 |
| ORDIM/ORDDATA | Oracle Multimedia ORDDATA Components | 13.875 |
| LOGMNR | LogMiner | 13.375 |
| WM | Workspace Manager | 7.5 |
| SM/OTHER | Server Manageability - Other Components | 6.6875 |
| SMON_SCN_TIME | Transaction Layer - SCN to TIME mapping | 4.25 |
| EXPRESSION_FILTER | Expression Filter System | 4 |
| SM/OPTSTAT | Server Manageability - Optimizer Statistics History | 3.5 |
| EM_MONITORING_USER | Enterprise Manager Monitoring User | 2 |
| SQL_MANAGEMENT_BASE | SQL Management Base Schema | 1.6875 |
| XSOQHIST | OLAP API History Tables | 1.5 |
| AO | Analytical Workspace Object Table | 1.5 |
| LOGSTDBY | Logical Standby | 1.375 |
| STREAMS | Oracle Streams | 1 |
| ORDIM | Oracle Multimedia ORDSYS Components | 0.5625 |
| AUTO_TASK | Automated Maintenance Tasks | 0.3125 |
| ORDIM/SI_INFORMTN_SCHEMA | Oracle Multimedia SI_INFORMTN_SCHEMA Components | 0 |
| TSM | Oracle Transparent Session Migration User | 0 |
| ULTRASEARCH_DEMO_USER | Oracle Ultra Search Demo User | 0 |
| ULTRASEARCH | Oracle Ultra Search | 0 |
| ORDIM/ORDPLUGINS | Oracle Multimedia ORDPLUGINS Components | 0 |
| XSAMD | OLAP Catalog | 0 |
Any help would be appreciated
Thanks