以前一些使用独立表空间或系统表空间的数据库组件现在在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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/248644/viewspace-991085/