The SYSAUX(System Auxiliary) tablespace is a new feature of Oracle 10g database,before 10g release,there wasn’t this tablespace.After you created your database via DBCA(Database Configuration Assistant) tool,the SYSAUX tablespace was created for you automatically.

But what’s the mainly function of that tablespace? Why Oracle present the SYSAUX tablespace? Does it bring some benefit for the database or the DBA?

Yep,the answer is ture.The SYSAUX tablespace is an auxiliary tablespace of the SYSTEM tablespace.In the earlier release before 10g,there is only way to store the components and other metadata in the SYSTEM tablespace.But now,with the SYSAUX tablespace,Oracle separate some components from SYSTEM tablespace,bring ’sth’ from the SYSTEM tablespace to SYSAUX tablespace.By that,the workload of SYSTEM tablespace becomes light,decreases the space fragment in the SYSTEM tablespace,and it becomes  easy to manage the SYSTEM tablespace for DBAs.

In addition,if the status of SYSAUX tablespace becomes offline(in fact we can bring it offline explicitly) or invalid,the Oracle database can works normally still.

Now,let’s find out which components were stored in the SYSTEM tablespace logically.First,we should be familar with the V$SYSAUX_OCCUPANTS dictionary table. V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.

So,we  need to do is only to issue a query against of the V$SYSAUX_OCCUPANTS view.

SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes from v$sysaux_occupants;

OCCUPANT_NAME          SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES

———————- —————— ——————————– ——————

LOGMNR                 SYSTEM             SYS.DBMS_LOGMNR_D.SET_TABLESPACE               6080

LOGSTDBY               SYSTEM             SYS.DBMS_LOGSTDBY.SET_TABLESPACE                896

STREAMS                SYS                                                                512

XDB                    XDB                XDB.DBMS_XDB.MOVEXDB_TABLESPACE               49728

AO                     SYS                DBMS_AW.MOVE_AWMETA                           20160

XSOQHIST               SYS                DBMS_XSOQ.OlapiMoveProc                       20160

XSAMD                  OLAPSYS            DBMS_AMD.Move_OLAP_Catalog                    15936

SM/AWR                 SYS                                                              31552

SM/ADVISOR             SYS                                                               5824

SM/OPTSTAT             SYS                                                              29760

SM/OTHER               SYS                                                               7488

OCCUPANT_NAME          SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES

———————- —————— ——————————– ——————

STATSPACK              PERFSTAT                                                             0

ODM                    DMSYS              MOVE_ODM                                        256

SDO                    MDSYS              MDSYS.MOVE_SDO                                33216

WM                     WMSYS              DBMS_WM.move_proc                              7040

ORDIM                  ORDSYS                                                             512

ORDIM/PLUGINS          ORDPLUGINS                                                           0

ORDIM/SQLMM            SI_INFORMTN_SCHEMA                                                   0

EM                     SYSMAN             emd_maintenance.move_em_tblspc                56896

TEXT                   CTXSYS             DRI_MOVE_CTXSYS                                4736

ULTRASEARCH            WKSYS              MOVE_WK                                           0

ULTRASEARCH_DEMO_USER  WK_TEST            MOVE_WK                                           0

OCCUPANT_NAME          SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES

———————- —————— ——————————– ——————

EXPRESSION_FILTER      EXFSYS                                                            3712

EM_MONITORING_USER     DBSNMP                                                            1600

TSM                    TSMSYS                                                             256

JOB_SCHEDULER          SYS                                                                512

26 rows selected.

SQL>

For a test purpose,we can bring the LogMiner component to the EXAMPLES tablespace.

SQL> exec dbms_logmnr_d.set_tablespace(’example’);

PL/SQL procedure successfully completed

and then we issue a  query against the V$SYSAUX_OCCUPANTS to obtain the effect:

SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes from v$sysaux_occupants where occupant_name like ‘LOG%’;

OCCUPANT_NAME          SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES

———————- —————— ——————————– ——————

LOGMNR                 SYSTEM             SYS.DBMS_LOGMNR_D.SET_TABLESPACE                  0

LOGSTDBY               SYSTEM             SYS.DBMS_LOGSTDBY.SET_TABLESPACE                896

ok,the SPACE_USAGE_KBYTES value of the LOGMNR   is 0,instead of  the original value of 6080.

last,let’s bring it back to the SYSAUX tablespace.

SQL> exec dbms_logmnr_d.set_tablespace(’SYSAUX’);

PL/SQL procedure successfully completed.

Furthermore,we can do some more experiences on the SYSAUX tablespce.

SQL> drop tablespace sysaux ;

drop tablespace sysaux

*

ERROR at line 1:

ORA-13501: Cannot drop SYSAUX tablespace

we can’t drop the SYSAUX tablespace.

SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

we can switch the status of the SYSAUX tablespace manually.

SQL> alter tablespace sysaux read only;

alter tablespace sysaux read only

*

ERROR at line 1:

ORA-13505: SYSAUX tablespace can not be made read only

we can not alter the SYSAUX tablespace to read only status.

SQL> alter tablespace sysaux rename to new_sys;

alter tablespace sysaux rename to new_sys

*

ERROR at line 1:

ORA-13502: Cannot rename SYSAUX tablespace

Also,we can not rename the SYSAUX tablespace.