32、SYSAUX表空間
以前一些使用獨立表空間或系統表空間的資料庫元件現在在SYSAUX表空間中建立.
透過分離這些元件和功能,SYSTEM表空間的負荷得以減輕.反覆建立一些相關物件及元件引起SYSTEM表空間的碎片問題得以避免。
如果SYSAUX表空間不可用,資料庫核心功能將保持有效;使用SYSAUX表空間的特點將會失敗或功能受限.
我們看一下資料庫建立指令碼:
CREATE DATABASE "eygle" |
以下是使用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表空間具有如下限制:
|
如果希望轉移這些系統物件的表空間,可以使用相應得系統包實現:
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/248644/viewspace-991085/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle清理SYSAUX表空間OracleUX
- ORACLE的SYSAUX 表空間OracleUX
- 2.5.4.1 關於SYSAUX表空間UX
- 認識 SYSAUX 表空間(zt)UX
- SYSAUX表空間清理之SM/OPTSTATUX
- AWR佔用sysaux表空間太大UX
- oracle之 SYSAUX表空間維護OracleUX
- SYSAUX表空間管理及恢復UX
- 4.2.1.7 規劃 SYSTEM 和 SYSAUX 表空間UX
- sysaux 表空間爆滿處理方法UX
- sysaux 表空間不足問題處理UX
- 修復受損的SYSAUX表空間UX
- 10G 新特性系列: SYSAUX 表空間UX
- 從system/sysaux空間轉移TABLE&Index到其它表空間UXIndex
- oracle sysaux表空間滿了處理辦法OracleUX
- 10g ORACLE_HOME空間滿導致SYSAUX表空間離線OracleUX
- AWR不自動刪除導致SYSAUX表空間滿UX
- 記一次sysaux表空間壞塊修復UX
- sysaux表空間檔案損壞的處理(zt)UX
- SYSAUX表空間使用率高問題處理UX
- 計算sysaux中各主件對sysaux空間的使用!UX
- 2.5.4 為 SYSAUX 表空間指定資料檔案屬性UX
- SYSAUX 表空間歷史統計資料過大purgeUX
- Oracle SYSAUX表空間使用率超過警戒閥值OracleUX
- 【AWR】該怎樣清理SYSAUX表空間相關資料UX
- Oracle10g以上sysaux表空間的維護和清理OracleUX
- Oracle SYSAUX 表空間使用率100% 導致的DB 故障OracleUX
- Oracle案例08——xx.xx.xx.xx,表空間 SYSAUX 使用率>95%%OracleUX
- SYSAUX表空間滿對資料庫的影響以及解決措施UX資料庫
- oracle10g的sysaux空間暴增與空間回收-轉載OracleUX
- 【AWR】清理SYSAUX表空間資料(第三季非暴力不合作)UX
- AWR資料導致SYSAUX表空間一直增長的問題UX
- oracle 10g SYSAUX表空間快速增長之WRH$_SQL_PLAN篇Oracle 10gUXSQL
- OGG相關的CPATURE導致SYSAUX表空間異常暴增處理UX
- SYSAUX表空間佔用過大情況下的處理(AWR資訊過多)UX
- oracle 11g sysaux表空間使用率非常高的問題解決OracleUX
- 【AWR】該怎樣清理SYSAUX表空間相關資料(第二季)UX
- ORACLE 10g SYSAUX表空間快速增長之WRH$_ACTIVE_SESSION_HISTORY篇Oracle 10gUXSession