1. 首页
  2. IT资讯

Oracle10g新特性-SYSAUX表空间

sysaux是system的补充表空间,把以前使用独立和system表空间的一些数据库组件存放在该表空间中,减少了system的负载和因为反复创建一些对象和组件造成的system对应数据文件块的不连续(表空间的碎片)情况的出现

http://www.eygle.com/10g/10g_sysaux_tbs.htm

[@more@]SYSAUX表空间在Oracle Database 10g中引入,作为SYSTEM表空间的辅助表空间.以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建.通过分离这些组件和功能,SYSTEM表空间的负荷得以减轻.反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免。如果SYSAUX表空间不可用,数据库核心功能将保持有效;使用SYSAUX表空间的特点将会失败或功能受限.

我们看一下数据库创建脚本:

CREATE DATABASE “eygle”MAXINSTANCES 8MAXLOGHISTORY 1MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100DATAFILE ‘/opt/oracle/oradata/eygle/system01.dbf’ SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCALSYSAUX 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 UNLIMITEDUNDO TABLESPACE “UNDOTBS1” DATAFILE ‘/opt/oracle/oradata/eygle/undotbs01.dbf’ SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITEDCHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16LOGFILE 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 10240KUSER 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 onlySQL> 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/450962/viewspace-1027900/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/194730.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code