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.
111
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production 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 6144 LOGSTDBY SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE 896 STREAMS SYS 512 XDB XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE 49216 AO SYS DBMS_AW.MOVE_AWMETA 768 XSOQHIST SYS DBMS_XSOQ.OlapiMoveProc 768 XSAMD OLAPSYS DBMS_AMD.Move_OLAP_Catalog 0 SM/AWR SYS 250496 SM/ADVISOR SYS 176384 SM/OPTSTAT SYS 289216 SM/OTHER SYS 23424 OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES ------------------------------ -------------------- ----------------------------------- ------------------ STATSPACK PERFSTAT 0 ODM DMSYS MOVE_ODM 0 SDO MDSYS MDSYS.MOVE_SDO 22400 WM WMSYS DBMS_WM.move_proc 7296 ORDIM ORDSYS 512 ORDIM/PLUGINS ORDPLUGINS 0 ORDIM/SQLMM SI_INFORMTN_SCHEMA 0 EM SYSMAN emd_maintenance.move_em_tblspc 164800 TEXT CTXSYS DRI_MOVE_CTXSYS 0 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 1856 TSM TSMSYS 0 JOB_SCHEDULER SYS 1024 26 rows selected. SQL>
222 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.
SQL>
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 2 from v$sysaux_occupants 3 where occupant_name like 'LOGMNR%' 4 ; OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES ------------------------------ -------------------- ----------------------------------- ------------------ LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 0 SQL>
333 ok,the SPACE_USAGE_KBYTES value of the LOGMNR is 0,instead of the original value of 6144.
and then,let's bring it back to the SYSAUX tablespace.
SQL> exec dbms_logmnr_d.set_tablespace('SYSAUX');
PL/SQL procedure successfully completed.
SQL> select occupant_name,schema_name,move_procedure,space_usage_kbytes
2 from v$sysaux_occupants
3 where occupant_name like 'LOGMNR%'
4 ;
OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES
------------------------------ -------------------- ----------------------------------- ------------------
LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 6144
SQL>
SQL> drop tablespace sysaux; drop tablespace sysaux * ERROR at line 1: ORA-13501: Cannot drop SYSAUX tablespace SQL>
555 we can't drop the SYSAUX tablespace.
SQL> alter tablespace sysaux offline; Tablespace altered. SQL> alter tablespace sysaux online; Tablespace altered. SQL>
666 we can switch the status(online to offline or else.) 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 SQL>
777 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 SQL>
888 Also,we can not rename the SYSAUX tablespace.