認識 SYSAUX 表空間(zt)

tolywang發表於2008-08-20
 
SYSAUX表空間在Oracle Database 10g中引入,作為SYSTEM表空間的輔助表空間. 

以前一些使用獨立表空間或系統表空間的資料庫元件現在在SYSAUX表空間中建立.
透過分離這些元件和功能,SYSTEM表空間的負荷得以減輕.反覆建立一些相關物件及元件引起SYSTEM表空間的碎片問題得以避免。

如果SYSAUX表空間不可用,資料庫核心功能將保持有效;使用SYSAUX表空間的特點將會失敗或功能受限.

我們看一下資料庫建立指令碼:


CREATE DATABASE "eygle"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/opt/oracle/oradata/eygle/system01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/opt/oracle/oradata/eygle/sysaux01.dbf'
SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED--SYSAUX表空間的建立
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/opt/oracle/oradata/eygle/temp01.dbf' SIZE 20M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/opt/oracle/oradata/eygle/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/opt/oracle/oradata/eygle/redo01.log') SIZE 10240K,
GROUP 2 ('/opt/oracle/oradata/eygle/redo02.log') SIZE 10240K,
GROUP 3 ('/opt/oracle/oradata/eygle/redo03.log') SIZE 10240K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";

以下是使用SYSAUX表空間的資料庫元件:

 

 

使用SYSAUX表空間的元件				以前版本所在表空間
            Analytical Workspace Object Table	           SYSTEM
            Enterprise Manager Repository	               OEM_REPOSITORY
            LogMiner			                           SYSTEM
            Logical Standby		               	       SYSTEM
            OLAP API History Tables		               CWMLITE
            Oracle Data Mining		                   ODM
            Oracle Spatial		                       SYSTEM
            Oracle Streams		               	       SYSTEM
            Oracle Text			                       DRSYS
            Oracle Ultra Search		                   DRSYS
            Oracle interMedia ORDPLUGINS Components      SYSTEM
            Oracle interMedia ORDSYS Components          SYSTEM
            Oracle interMedia SI_INFORMTN_SCHEMA Components	SYSTEM
            Server Manageability Components	           New in Oracle Database 10g
            Statspack Repository		                   User-defined
            Unified Job Scheduler		                   New in Oracle Database 10g
            Workspace Manager	                           SYSTEM
            

 


新增的V$SYSAUX_OCCUPANTS檢視可以用來檢視這些資訊

 

 

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME
            2  from V$SYSAUX_OCCUPANTS;
            OCCUPANT_NAME OCCUPANT_DESC                                                    SCHEMA_NAME
            ------------- ---------------------------------------------------------------- ------------------
            LOGMNR        LogMiner                                                         SYSTEM
            LOGSTDBY      Logical Standby                                                  SYSTEM
            STREAMS       Oracle Streams                                                   SYS
            AO            Analytical Workspace Object Table                                SYS
            XSOQHIST      OLAP API History Tables                                          SYS
            SM/AWR        Server Manageability - Automatic Workload Repository             SYS
            SM/ADVISOR    Server Manageability - Advisor Framework                         SYS
            SM/OPTSTAT    Server Manageability - Optimizer Statistics History              SYS
            SM/OTHER      Server Manageability - Other Components                          SYS
            STATSPACK     Statspack Repository                                             PERFSTAT
            ODM           Oracle Data Mining                                               DMSYS
            OCCUPANT_NAME OCCUPANT_DESC                                                    SCHEMA_NAME
            ------------- ---------------------------------------------------------------- ------------------
            SDO           Oracle Spatial                                                   MDSYS
            WM            Workspace Manager                                                WMSYS
            ORDIM         Oracle interMedia ORDSYS Components                              ORDSYS
            ORDIM/PLUGINS Oracle interMedia ORDPLUGINS Components                          ORDPLUGINS
            ORDIM/SQLMM   Oracle interMedia SI_INFORMTN_SCHEMA Components                  SI_INFORMTN_SCHEMA
            EM            Enterprise Manager Repository                                    SYSMAN
            TEXT          Oracle Text                                                      CTXSYS
            ULTRASEARCH   Oracle Ultra Search                                              WKSYS
            JOB_SCHEDULER Unified Job Scheduler                                            SYS
            20 rows selected.
            

 


SYAAUX表空間具有如下限制:

 

1. 不能刪除
SQL> drop tablespace SYSAUX including contents and datafiles;
drop tablespace SYSAUX including contents and datafiles
*
ERROR at line 1:
ORA-13501: Cannot drop SYSAUX tablespace

 

2. 不能重新命名
SQL> alter tablespace SYSAUX rename to OPT_TBS;
alter tablespace SYSAUX rename to OPT_TBS
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

 

3. 不能置為read only
SQL> alter tablesapce SYSAUX read only;
alter tablesapce SYSAUX read only
*
ERROR at line 1:
ORA-00940: invalid ALTER command

 

 

如果希望轉移這些系統物件的表空間,可以使用相應得系統包實現:

 

SQL> set linesize 120
            SQL> col schema_name for a18
            SQL> col occupant_name for a13
            SQL> col move_procedure for a32
            SQL> SELECT    occupant_name, schema_name, move_procedure,space_usage_kbytes
            2  FROM      v$sysaux_occupants
            3  ORDER BY  1
            4  /
            OCCUPANT_NAME SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES
            ------------- ------------------ -------------------------------- ------------------
            AO            SYS                DBMS_AW.MOVE_AWMETA. 768
            EM            SYSMAN             emd_maintenance.move_em_tblspc                    0
            JOB_SCHEDULER SYS                                                                256
            LOGMNR        SYSTEM             SYS.DBMS_LOGMNR_D.SET_TABLESPACE               7488------------注意這裡
            LOGSTDBY      SYSTEM             SYS.DBMS_LOGSTDBY.SET_TABLESPACE                  0
            ODM           DMSYS              MOVE_ODM                                          0
            ORDIM         ORDSYS                                                               0
            ORDIM/PLUGINS ORDPLUGINS                                                           0
            ORDIM/SQLMM   SI_INFORMTN_SCHEMA                                                   0
            SDO           MDSYS              MDSYS.MOVE_SDO                                    0
            SM/ADVISOR    SYS                                                               5760
            OCCUPANT_NAME SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES
            ------------- ------------------ -------------------------------- ------------------
            SM/AWR        SYS                                                              62848
            SM/OPTSTAT    SYS                                                               9344
            SM/OTHER      SYS                                                               2816
            STATSPACK     PERFSTAT                                                             0
            STREAMS       SYS                                                                192
            TEXT          CTXSYS             DRI_MOVE_CTXSYS                                   0
            ULTRASEARCH   WKSYS              MOVE_WK                                           0
            WM            WMSYS              DBMS_WM.move_proc                              6656
            XSOQHIST      SYS                DBMS_XSOQ.OlapiMoveProc                         768
            20 rows selected.
            SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('USERS');
            PL/SQL procedure successfully completed.
            SQL> set linesize 120
            SQL> col schema_name for a18
            SQL> col occupant_name for a13
            SQL> col move_procedure for a32
            SQL> SELECT    occupant_name, schema_name, move_procedure,space_usage_kbytes
            2  FROM      v$sysaux_occupants
            3  ORDER BY  1
            4  /
            OCCUPANT_NAME SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES
            ------------- ------------------ -------------------------------- ------------------
            AO            SYS                DBMS_AW.MOVE_AWMETA. 768
            EM            SYSMAN             emd_maintenance.move_em_tblspc                    0
            JOB_SCHEDULER SYS                                                                256
            LOGMNR        SYSTEM             SYS.DBMS_LOGMNR_D.SET_TABLESPACE                  0------------注意這裡
            LOGSTDBY      SYSTEM             SYS.DBMS_LOGSTDBY.SET_TABLESPACE                  0
            ODM           DMSYS              MOVE_ODM                                          0
            ORDIM         ORDSYS                                                               0
            ORDIM/PLUGINS ORDPLUGINS                                                           0
            ORDIM/SQLMM   SI_INFORMTN_SCHEMA                                                   0
            SDO           MDSYS              MDSYS.MOVE_SDO                                    0
            SM/ADVISOR    SYS                                                               5760
            OCCUPANT_NAME SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES
            ------------- ------------------ -------------------------------- ------------------
            SM/AWR        SYS                                                              62848
            SM/OPTSTAT    SYS                                                               9344
            SM/OTHER      SYS                                                               2816
            STATSPACK     PERFSTAT                                                             0
            STREAMS       SYS                                                                192
            TEXT          CTXSYS             DRI_MOVE_CTXSYS                                   0
            ULTRASEARCH   WKSYS              MOVE_WK                                           0
            WM            WMSYS              DBMS_WM.move_proc                              6656
            XSOQHIST      SYS                DBMS_XSOQ.OlapiMoveProc                         768
            20 rows selected.

 

復位:


 

SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX');
            PL/SQL procedure successfully completed.
            SQL> set linesize 120
            SQL> col schema_name for a18
            SQL> col occupant_name for a13
            SQL> col move_procedure for a32
            SQL> SELECT    occupant_name, schema_name, move_procedure,space_usage_kbytes
            2  FROM      v$sysaux_occupants
            3  ORDER BY  1
            4  /
            OCCUPANT_NAME SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES
            ------------- ------------------ -------------------------------- ------------------
            AO            SYS                DBMS_AW.MOVE_AWMETA. 768
            EM            SYSMAN             emd_maintenance.move_em_tblspc                    0
            JOB_SCHEDULER SYS                                                                256
            LOGMNR        SYSTEM             SYS.DBMS_LOGMNR_D.SET_TABLESPACE               7488
            LOGSTDBY      SYSTEM             SYS.DBMS_LOGSTDBY.SET_TABLESPACE                  0
            ODM           DMSYS              MOVE_ODM                                          0
            ORDIM         ORDSYS                                                               0
            ORDIM/PLUGINS ORDPLUGINS                                                           0
            ORDIM/SQLMM   SI_INFORMTN_SCHEMA                                                   0
            SDO           MDSYS              MDSYS.MOVE_SDO                                    0
            SM/ADVISOR    SYS                                                               5760
            OCCUPANT_NAME SCHEMA_NAME        MOVE_PROCEDURE                   SPACE_USAGE_KBYTES
            ------------- ------------------ -------------------------------- ------------------
            SM/AWR        SYS                                                              62848
            SM/OPTSTAT    SYS                                                               9344
            SM/OTHER      SYS                                                               2816
            STATSPACK     PERFSTAT                                                             0
            STREAMS       SYS                                                                192
            TEXT          CTXSYS             DRI_MOVE_CTXSYS                                   0
            ULTRASEARCH   WKSYS              MOVE_WK                                           0
            WM            WMSYS              DBMS_WM.move_proc                              6656
            XSOQHIST      SYS                DBMS_XSOQ.OlapiMoveProc                         768
            20 rows selected.
            


 

結論:

這是一個管理及規劃上的改進,進一步獨立SYSTEM表空間,保證其儲存及效能.

我們在做資料庫規劃時大可借鑑Oracle這個改進,分離重要資料及次要資料,分離穩定結構及頻繁變化結構,儘量減少對重要資料及結構的影響。

 

作者: | 連結:  

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-430059/,如需轉載,請註明出處,否則將追究法律責任。

相關文章