SYSAUX

SYSAUX

SYSAUX Tablespace

SYSAUX Tablespace is new feature in 10g.  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, But the database features that use the SYSAUX tablespace could fail, or function with limited capability. You cannot rename the SYSAUX tablespace nor can you drop it. Trying to perform either action will generate an error.

Transportable tablespaces for SYSAUX is not supported.

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

OCCUPANTS OF THE SYSAUX TABLESPACE

 

The below table display various features of the database that now use the SYSAUX tablespace, that earlier might have created their own tablespaces.

Occupant	Original Tablespace
Text, Ultra search	DRSYS
Intermedia, Spatial	SYSTEM
OLAP	CWMLITE
Workspace Manager	SYSTEM
Data Mining	ODM
Recovery Catalog	TOOLS
EM Repository	OEM_REPOSITORY

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 10 concurrent active sessions may require approximately 200 to 300 MB of space for its AWR data.

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.

Assume that you install 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. You would issue a statement like:

SQL> EXEC WKSYS.MOVE_WK('DRSYS');

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

SQL> EXEC WKSYS.MOVE_WK('SYSAUX'); 

The occupants that cannot be relocated are STREAMS, SMC, STATSPACK, ORDIM, ORDIM/PLUGINS, ORDIM/SQLMM and JOB_SCHEDULER.

The following table provides guidelines on sizing the SYSAUX tablespace based on the system configuration and expected load.

Parameter/Recommendation	Small	Medium	Large
Number of CPUs	2	8	32
Number of concurrently active sessions	10	20	100
Number of user objects: tables and indexes	500	5000	50000
Estimated SYSAUX size at steady state with default configuration	500MB	2 GB	5 GB

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 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 UltraSearch, 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.

BENEFITS OF THE SYSAUX TABLESPACE

Reduced number of tablespaces: As mentioned earlier, some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace. For example the XDB and OLAP require their own tablespace as part of the feature installation. Now in Oracle 10g, the need for these additional tablespaces has been removed. The SYSAUX tablespace is the default tablespace for these Oracle features.

Easier RAC management: For RAC users who are using raw devices, a separate raw device had to be allocated for each tablespace. Managing these raw devices can be quite a difficult task for a DBA. By consolidating the tablespaces into a single SYSAUX tablespace, the need for many raw devices has also reduced.

Load Reduction on the SYSTEM tablespace – Since data is now shared with the SYSAUX tablespace there is an improvement in performance of the SYSTEM tablespace.