Managing the sysaux tablespace - Oracle DBA

The sysaux tablespace was installed as an auxiliary tablespace to the system tablespace when you created your database. Some database components that formerly created and used separate TABLESPACES now occupy the sysaux tablespace.

If the sysaux tablespace becomes unavailable, core DATABASE functionality will remain operational. The database features that use the sysaux tablespace could fail, or function with limited capability.

Monitoring occupants of the SYSAUX TABLESPACE:

There are certain list of registered occupants of the sysaux tablespace. These components can use the sysaux tablespace, and their installation provides the means of establishing their occupancy of the sysaux tablespace.

You can monitor the occupants of the sysaux tablespace using the v$sysaux_occupants view. This view lists the following information about the occupants of the sysaux tablespace:

• Name of the occupant
• Occupant description
• Schema name
• Move procedure
• Current space usage

View information is maintained by the occupants.

Moving occupants out of or into the sysaux tablespace:

You will have an option at component install time to specify that you do not want the component to reside in sysaux. Also, if you later decide that the component should be relocated to a designated tablespace, you can use the move procedure for that component, as specified in the v$sysaux_occupants view, to perform the move.

For example, assume that you install an oracle ultra search into the default tablespace, which is sysaux. Later you discover that ultra search is using up too much space. To alleviate this space pressure on sysaux, you can call a pl/sql move procedure specified in the v$sysaux_occupants view to relocate ultra search to another tablespace.

The move procedure also lets you move a component from another tablespace into the sysaux tablespace.

Checkout Oracle DBA Interview Questions

Controlling the size of the sysaux tablespace:

The sysaux tablespace is occupied by a number of database components, and its total size is governed by the space consumed by those components. The space consumed by the components, in turn, depends on which features or functionality are being used and on the nature of the database workload.

The largest portion of the sysaux tablespace is occupied by the automatic workload repository (awr). The space consumed by the awr is determined by several factors, including the number of active sessions in the system at any given time, the snapshot interval, and the historical data retention period. A typical system with an average of 30 concurrent active sessions may require approximately 200 to 300 MB of space for its awr data. You can control the size of the awr by changing the snapshot interval and historical data retention period.

Another major occupant of the sysaux tablespace is the embedded enterprise manager (em) repository. This repository is used by oracle enterprise manager database control to store its metadata. The size of this repository depends on database activity and on the configuration-related information stored in the repository.

Other database components in the sysaux tablespace will grow in size only if their associated features (for example, oracle ultra search, oracle text, oracle streams) are in use. If the features are not used, then these components do not have any significant effect on the size of the sysaux tablespace.