SYSAUX表空间详解_无知的世界

http://blog.sina.com.cn/u/2778876875

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

SYSAUX表空间详解

(2012-11-26 23:37:29)

标签：

oracle

sysaux

表空间

it

分类： oracle

sysaux表空间在oralce database 10g中引用，作为system表空间的辅助表空间。

以前一些使用独立表空间或系统表空间的数据库组件先做着sysaux表空间中创建。

为了简化管理支持oracle特性的对象，10g版本中增加了另一个强制的表空间SYSAUX，它包含一些以前位于system表空间中的对象，并且集中存储支持oracle特性(例如LogMiner、UltraSearch和DataMining)的对象，这些对象提供了新的视图v$sysaux_occupants,其中显示了每个组件使用的空间数量以及oracle提供的存储过程的名称，可以使用这些存储过程将对象移入和移出SYSAUX表空间。管理员可以通过这些存储过程将支持特定特性的对象移到sysaux以外的表空间。表1-1显示了以前版本中一些sysaux组件的存储位置。

需要注意的是，表1-1没有显示使用的所有模式。例如，OLAP也使用OLAPSYS模式；Intermedia和Spatial使用MDDATA、MDSYS、ORDSYS和ORDPLUGINS模式；EM也使用DBSNMP模式。使用SYSAUX表空间可避免SYSTEM表空间中在安装和卸载oracle选项时产生的碎片。

表1-1 SYSAUX表空间组件以前的位置

----------------------------------------------------------------------------

特性 | 模式 | 以前的表空间

----------------------------------------------------------------------------

OLAP | CWMLITE | CWMLITE

----------------------------------------------------------------------------

TEXT | CTXSYS | DRSYS

----------------------------------------------------------------------------

UltraSearch | WKSYS | DRSYS

----------------------------------------------------------------------------

Intermedia and Spatial | ORDSYS | SYSTEM

----------------------------------------------------------------------------

工作空间管理程序(Workspace Manager) | WMSYS | SYSTEM

----------------------------------------------------------------------------

数据挖掘(Data Mining) | DMSYS | ODM

----------------------------------------------------------------------------

EM仓库(EM Repository) | SYSMAN | OEM_REPOSITORY

----------------------------------------------------------------------------

Logminer | SYSTEM | SYSTEM

----------------------------------------------------------------------------

StatsPack | PERFSTAT | 用户指定

----------------------------------------------------------------------------

作业调度程序(Job Scheduler) | SYS | SYSTEM

----------------------------------------------------------------------------

--查看

set linesize 120 pages 999;

col schema_name for a20;

col occupant_name for a25;

select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME from V$SYSAUX_OCCUPANTS;

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME

------------------------- ---------------------------------------------------- --------------------

LOGMNR LogMiner SYSTEM

LOGSTDBY Logical Standby SYSTEM

STREAMS Oracle Streams SYS

XDB XDB XDB

AO Analytical Workspace Object Table SYS

XSOQHIST OLAP API History Tables SYS

XSAMD OLAP Catalog OLAPSYS

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

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

ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User WK_TEST

EXPRESSION_FILTER Expression Filter System EXFSYS

EM_MONITORING_USER Enterprise Manager Monitoring User DBSNMP

TSM Oracle Transparent Session Migration User TSMSYS

JOB_SCHEDULER Unified Job Scheduler SYS

SYAAUX表空间具有如下限制:

1、不能删除

2、不能重命名

3、不能设为read only

--==转移系统对象的表空间

--查看

set linesize 120 pages 999;

col schema_name for a20;

col occupant_name for a40;

col move_procedure for a32;

SELECT occupant_name, schema_name, move_procedure,space_usage_kbytes/1024

FROM v$sysaux_occupants

ORDER BY 1

OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES/1024

--------------------- -------------------- -------------------------------- -----------------------

AO SYS DBMS_AW.MOVE_AWMETA 19.6875

EM SYSMAN emd_maintenance.move_em_tblspc 55

EM_MONITORING_USER DBSNMP 1.5625

EXPRESSION_FILTER EXFSYS 3.625

JOB_SCHEDULER SYS .375

LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 5.9375 ***这里

LOGSTDBY SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE .875

ODM DMSYS MOVE_ODM .25

ORDIM ORDSYS .5

ORDIM/PLUGINS ORDPLUGINS 0

ORDIM/SQLMM SI_INFORMTN_SCHEMA 0

SDO MDSYS MDSYS.MOVE_SDO 40.5625

SM/ADVISOR SYS 5.125

SM/AWR SYS 29.625

SM/OPTSTAT SYS 8.125

SM/OTHER SYS 5.5625

STATSPACK PERFSTAT 0

STREAMS SYS .5

TEXT CTXSYS DRI_MOVE_CTXSYS 4.625

TSM TSMSYS .25

ULTRASEARCH WKSYS MOVE_WK 0

ULTRASEARCH_DEMO_USER WK_TEST MOVE_WK 0

WM WMSYS DBMS_WM.move_proc 6.875

XDB XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE 48.125

XSAMD OLAPSYS DBMS_AMD.Move_OLAP_Catalog 15.5625

XSOQHIST SYS DBMS_XSOQ.OlapiMoveProc 19.6875

--转移表空间

exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('USERS');

--然后再查看一次

set linesize 120 pages 999;

col schema_name for a20;

col occupant_name for a40;

col move_procedure for a32;

SELECT occupant_name, schema_name, move_procedure,space_usage_kbytes/1024

FROM v$sysaux_occupants

ORDER BY 1

OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES/1024

---------------------------------------- -------------------- -------------------------------- -----------------------

AO SYS DBMS_AW.MOVE_AWMETA 19.6875

EM SYSMAN emd_maintenance.move_em_tblspc 55

EM_MONITORING_USER DBSNMP 1.5625

EXPRESSION_FILTER EXFSYS 3.625

JOB_SCHEDULER SYS .375

LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 0 ***这里

LOGSTDBY SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE .875

ODM DMSYS MOVE_ODM .25

ORDIM ORDSYS .5

ORDIM/PLUGINS ORDPLUGINS 0

ORDIM/SQLMM SI_INFORMTN_SCHEMA 0

SDO MDSYS MDSYS.MOVE_SDO 40.5625

SM/ADVISOR SYS 5.125

SM/AWR SYS 29.75

SM/OPTSTAT SYS 8.125

SM/OTHER SYS 5.5625

STATSPACK PERFSTAT 0

STREAMS SYS .5

TEXT CTXSYS DRI_MOVE_CTXSYS 4.625

TSM TSMSYS .25

ULTRASEARCH WKSYS MOVE_WK 0

ULTRASEARCH_DEMO_USER WK_TEST MOVE_WK 0

WM WMSYS DBMS_WM.move_proc 6.875

XDB XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE 48.125

--复位

exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX');

--查看一次

set linesize 120 pages 999;

col schema_name for a20;

col occupant_name for a40;

col move_procedure for a32;

SELECT occupant_name, schema_name, move_procedure,space_usage_kbytes/1024

FROM v$sysaux_occupants

ORDER BY 1

OCCUPANT_NAME SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES/1024

---------------------------------------- -------------------- -------------------------------- -----------------------

AO SYS DBMS_AW.MOVE_AWMETA 19.6875

EM SYSMAN emd_maintenance.move_em_tblspc 55

EM_MONITORING_USER DBSNMP 1.5625

EXPRESSION_FILTER EXFSYS 3.625

JOB_SCHEDULER SYS .375

LOGMNR SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE 5.9375 **这里

LOGSTDBY SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE .875

ODM DMSYS MOVE_ODM .25

ORDIM ORDSYS .5

ORDIM/PLUGINS ORDPLUGINS 0

ORDIM/SQLMM SI_INFORMTN_SCHEMA 0

SDO MDSYS MDSYS.MOVE_SDO 40.5625

SM/ADVISOR SYS 5.125

SM/AWR SYS 29.75

SM/OPTSTAT SYS 8.125

SM/OTHER SYS 5.5625

STATSPACK PERFSTAT 0

STREAMS SYS .5

TEXT CTXSYS DRI_MOVE_CTXSYS 4.625

TSM TSMSYS .25

ULTRASEARCH WKSYS MOVE_WK 0

ULTRASEARCH_DEMO_USER WK_TEST MOVE_WK 0

WM WMSYS DBMS_WM.move_proc 6.875

XDB XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE 48.125

XSAMD OLAPSYS DBMS_AMD.Move_OLAP_Catalog 15.5625

XSOQHIST SYS DBMS_XSOQ.OlapiMoveProc 19.6875

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：在数据库中查询的IP地址

后一篇：oracle后台进程详解

新浪BLOG意见反馈留言板　欢迎批评指正