SYSAUX 表空间介绍

   (一)  sysaux表空间的一些介绍

         在Oracle 10g 版本中，引入了SYSTEM表空间的一个辅助表空间:SYSAUX表空间。

        SYSAUX 表空间存放一些其他的metadata组件，如OEM,Streams 等会默认存放在SYSAUX表空间里。这样也能降低SYSTEM表空间的负载。 因此SYSAUX 表空间也是在在DB 创建或者升级时自动创建的。 如果在手工使用SQL创建DB时没有指定SYSAUX tablespace，那么创建语句会报错。 无法执行。

       在正常操作下，不能drop 和rename sysaux表空间。如果sysaux表空间不可用时，数据库的核心功能还是可以继续运行的。只是一些存放在SYSAUX表空间里的功能受到限制，就如我们之前说的OEM。

 

    (二) 查看sysaux的具体信息

        通过下面的语句：select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual

          具体的内容如下：

                 
  CREATE TABLESPACE "SYSAUX" DATAFILE
  '/oracle/oradata/bsqqns/bsqqns/sysaux01.dbf' SIZE 125829120
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/oracle/oradata/bsqqns/bsqqns/sysaux01.dbf' RESIZE 335544320

       由上面可以看出表空间有下面几个属性 ：PERMANENT、  EXTENT MANAGEMENT LOCAL、SEGMENT SPACE MANAGEMENT AUTO

     (三)查看sysaux相关组件

             可以使用v$sysaux_occupants 视图来查看SYSAUX 表空间里的组件信息

     下面是在11g R2版本的数据库中查看得到：

SQL> select occupant_name,schema_name,move_procedure from v$sysaux_occupants;
 
OCCUPANT_NAME                                                    SCHEMA_NAME                                                      MOVE_PROCEDURE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
LOGMNR                                                           SYSTEM                                                           SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                                                         SYSTEM                                                           SYS.DBMS_LOGSTDBY.SET_TABLESPACE
STREAMS                                                          SYS                                                             
XDB                                                              XDB                                                              XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO                                                               SYS                                                              DBMS_AW.MOVE_AWMETA
XSOQHIST                                                         SYS                                                              DBMS_XSOQ.OlapiMoveProc
XSAMD                                                            OLAPSYS                                                          DBMS_AMD.Move_OLAP_Catalog
SM/AWR                                                           SYS                                                             
SM/ADVISOR                                                       SYS                                                             
SM/OPTSTAT                                                       SYS                                                             
SM/OTHER                                                         SYS                                                             
STATSPACK                                                        PERFSTAT                                                        
ODM                                                              DMSYS                                                            MOVE_ODM
SDO                                                              MDSYS                                                            MDSYS.MOVE_SDO
WM                                                               WMSYS                                                            DBMS_WM.move_proc
ORDIM                                                            ORDSYS                                                          
ORDIM/PLUGINS                                                    ORDPLUGINS                                                      
ORDIM/SQLMM                                                      SI_INFORMTN_SCHEMA                                              
EM                                                               SYSMAN                                                           emd_maintenance.move_em_tblspc
TEXT                                                             CTXSYS                                                           DRI_MOVE_CTXSYS
 
OCCUPANT_NAME                                                    SCHEMA_NAME                                                      MOVE_PROCEDURE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
ULTRASEARCH                                                      WKSYS                                                            MOVE_WK
ULTRASEARCH_DEMO_USER                                            WK_TEST                                                          MOVE_WK
EXPRESSION_FILTER                                                EXFSYS                                                          
EM_MONITORING_USER                                               DBSNMP                                                          
TSM                                                              TSMSYS                                                          
JOB_SCHEDULER                                                    SYS                                                             
 
26 rows selected
 

       注：从这种实例中，我们可以看出sysaux的一些作用。AWR 是SYSAUX 中占用空间最多的组件