Database Administration (MOSC)

MOSC Banner

Size of SYSAUX tablespace (from 11.2.0.1 to 11.2.0.4)

edited Sep 15, 2016 4:10PM in Database Administration (MOSC) 4 commentsAnswered

SYSAUX tablespace has grown to a size of 55G. Please see below space usage by component from awrinfo report.

SYSAUX occupants space usage:

SM/OPTSTAT           SYS                       42,114.8 MB

SM/AWR               SYS                       12,418.3 MB

I believe it is due a known issue of system not being able to purge records. Tables SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY and SYS.WRI$_OPTSTAT_HISTGRM_HISTORY are of size 3.7 and 9.7G respectively with million of records.

I recently upgraded database from 11.2.0.1 to 11.2.0.4.

In another database of same version, above two tables are partitioned and reasonably sized. Do i need to run any SQL script or perform any changes to database so that partitions are created and data is purged properly. Isn't it supposed to take care of this issue as soon as database is upgraded? Is it still an issue in 11.2.0.4 version as well? Please share your valuable input if anyone experiencing a same issue and know a solution.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center