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/，如需轉載，請註明出處，否則將追究法律責任。