Chinaunix首页 | 论坛 | 博客
  • 博客访问: 267433
  • 博文数量: 110
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 115
  • 用 户 组: 普通用户
  • 注册时间: 2016-07-22 15:26
个人简介

10多年的信息工作,随时记录心得和资料,希望能结识更多的朋友

文章分类

全部博文(110)

文章存档

2017年(12)

2016年(12)

2015年(14)

2014年(12)

2009年(15)

2008年(45)

我的朋友

分类: Oracle

2008-07-15 13:30:41

为了避免干扰数据库对system表空间的干扰,增加了sysaux表空间,具有一些特性
 
以下是使用SYSAUX表空间的数据库组件:
  
  代码:
  
  --------------------------------------------------------------------------------
  以下是使用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                   &n
  
  新增的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: inval
   
  如果希望转移这些系统对象的表空间,可以使用相应得系统包实现:
  
  代码:
  
  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          
 
阅读(1640) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~