The SYSAUX Tablespace

The SYSAUX tablespace is a new tablespace component in Oracle10g. When you upgrade your Oracle database to Oracle 10g, you need to create a new tablespace called SYSAUX. Also, when you create your first Oracle 20g database, Oracle will create SYSAUX.

The SYSAUX tablespace is a new tablespace that is required in Oracle Database 10g. SYSAUX is a secondary tablespace for storage of a number of database components that were previously stored in the SYSTEM tablespace. It is created as a locally managed tablespace using automatic segment space management.

The SYSAUX Tablespace should have a minimum size of 250M. SYSAUX Tablespace needs to be PERMANENT with extent management local and ASSM (Automatic Segment Space Management). The SYSAUX Tablespace cannot be made read only. Hence, proper care should be taken while creating the SYSAUX tablespace as the tablespace attributes are not modifiable once these are set.

Previously, many Oracle features required their own separate tablespaces (such as the RMAN recovery catalog, Ultra Search, Data Mining, XDP, and OLAP). This increases the management responsibility of the DBA. The SYSAUX tablespace consolidates these tablespaces into one location, which becomes the default tablespace for these Oracle features.

By default, when you create an Oracle database, Oracle creates the SYSAUX tablespace for you. If you are using Oracle Managed Files (OMF), the tablespace is created in the appropriate OMF directory. If you use the sysaux datafile clause in the CREATE DATABASE statement, the SYSAUX tablespace datafile(s) will be created in the location you define. Finally, if no sysaux datafile clause is included and OMF is not configured, Oracle creates the SYSAUX tablespace in a default location that is OS-specific. Here is an example of a create database statement with the sysaux datafile clause in it:

CREATE DATABASE my_db
DATAFILE 'c:\oracle\oradata\my_db\my_db_system_01.dbf' SIZE 300m
SYSAUX DATAFILE 'c:\oracle\my_db\my_db_sysaux_01.dbf' SIZE 100m
DEFAULT TEMPORARY TABLESPACE dtemp_tbs tempfile
'c:\oracle\my_db\my_db_temp_01.dbf' SIZE 100m
UNDO TABLESPACE undo_tbs_one DATAFILE
'c:\oracle\my_db\my_db_undo_tbs_one_01.dbf' SIZE 100m;

When you migrate to Oracle Database 10g, the SYSAUX tablespace needs to be created as a part of that migration. This is done after mounting the database under the new Oracle Database 10g database software. Once you have mounted it, open the database in migrate mode with the startup migrate command. After the database is open, you can create the SYSAUX tablespace. Here is the create tablespace statement to use in order to perform this operation:

CREATE TABLESPACE sysaux
DATAFILE 'c:\oracle\oradata\my_db\my_db_sysaux_01.dbf' SIZE 300m
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The SYSAUX tablespace must be created with the attributes shown in the preceding example. The following restrictions apply to the usage of the SYSAUX tablespace:

When migrating to Oracle 10g, you can create the SYSAUX tablespace only when the database is open in migrate mode.
When migrating to Oracle 10g, if a tablespace is already named SYSAUX, you need to remove it or rename it while you are in migrate mode.
Once you have opened your Oracle 10g database, you cannot drop the SYSAUX tablespace. If you try, an error will be returned.
You cannot rename the SYSAUX tablespace during normal database operations.
The SYSAUX tablespace cannot be transported to other databases via Oracle's transportable tablespace feature. Once the SYSAUX tablespace is in place and the database has been upgraded, you can add or resize datafiles associated with a SYSAUX tablespace just as you would any other tablespace through the ALTER TABLESPACE command, as shown in this example:
```
ALTER TABLESPACE sysaux ADD DATAFILE
 'd:\oracle\oradata\my_db\my_db_sysaux_01.dbf' SIZE 200M;
```

Managing Occupants of the SYSAUX Tablespace

Each set of application tables within the SYSAUX tablespace is known as an occupant. The V$SYSAUX_OCCUPANTS view manages occupants in the SYSAUX tablespace. This view allows you to monitor the space usage of occupant application objects in the SYSAUX tablespace, as shown in this example:

SELECT occupant_name, space_usage_kbytes FROM v$sysaux_occupants;

In this case, Oracle will display the space usage for the occupants, such as the RMAN recovery catalog.

If you determine that you need to move the occupants out of the SYSAUX tablespace, then the MOVE_PROCEDURE column of the V$SYSAUX_OCCUPANTS view will indicate the procedure that you should use to move the related occupant from the SYSAUX tablespace to another tablespace. This can also be a method of "reorganizing" your component object tables, should that be required. Note: The loss of the SYSAUX tablespace is not fatal to your database. In our testing it appears that the only real impact is that certain functionality related to the occupants of the SYSAUX tablespace is lost.

Retrieved from "http://dbpedias.com/w/index.php?title=Oracle:The_SYSAUX_Tablespace&oldid=7964"