High Table space usage in SYSAUX

In general, DBA should focus on the SYSTEM, SYSAUX, UNDO, TEMP, and SYSTEM tablespaces in addition to the tablespace where business data is stored, the size of the UNDO and TEMP tablespace is determined by the business conditions of the database.

In general, DBA should focus on the SYSTEM, SYSAUX, UNDO, TEMP, and SYSTEM tablespaces in addition to the tablespace where business data is stored, the size of the UNDO and TEMP tablespace is determined by the business conditions of the database.

In general, DBA should focus on the SYSTEM, SYSAUX, UNDO, TEMP, and SYSTEM tablespaces in addition to the tablespace where business data is stored, the size of the UNDO and TEMP tablespaces is determined by the business conditions of the database. If you do not configure the SYSAUX tablespaces by default, they will expand over time! The SYSAUX tablespace is an auxiliary tablespace of the system tablespace. It mainly stores EM-related content and table statistics, AWR snapshots, and audit information. In my opinion, the server space, if the size of your SYSAUX tablespace exceeds 2 GB, you should let him lose weight!

I. Use the following statements to query table space usage

Ii. query the proportion of each category in the SYSAUX tablespace to the storage space. The Hong Kong virtual host obviously shows that AWR snapshots occupy about 2 GB of space and the statistical information is about MB, at the same time, the database has disabled audit audit_trail and the Hong Kong virtual host, so the audit table aud $ does not occupy space.

3. Modify the retention time of statistical information. The default value is 31 days. Here, the value is changed to 7 days. Expired statistical information is automatically deleted.

4. Change the AWR snapshot retention period to 7 days (7*24*60). The snapshot is collected every hour. You can also view and modify the snapshot on the EM page.

5. Delete the AWR snapshot and view the SYSAUX tablespace usage again. The tablespace usage is reduced to 38.42%.

Thank you for sharing your reference!

This article is from the "Bo Yue" blog and will not be reproduced!