All about Oracle10g SYSAUX tablespace
来源:互联网 发布:淘宝开店怎么上架 编辑:程序博客网 时间:2024/06/06 00:02
SYSAUX表空间在Oracle Database 10g中引入,作为SYSTEM表空间的辅助表空间.
以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建.
通过分离这些组件和功能,SYSTEM表空间的负荷得以减轻.反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免。
如果SYSAUX表空间不可用,数据库核心功能将保持有效;使用SYSAUX表空间的特点将会失败或功能受限.
我们看一下数据库创建脚本:
====================================================================
CREATE DATABASE "eygle"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/opt/oracle/oradata/eygle/system01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX 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 UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/opt/oracle/oradata/eygle/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 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 10240K
USER 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 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
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.
11G中又加入了新组件。
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: 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
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
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表空间,保证其存储及性能.
以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建.
通过分离这些组件和功能,SYSTEM表空间的负荷得以减轻.反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免。
如果SYSAUX表空间不可用,数据库核心功能将保持有效;使用SYSAUX表空间的特点将会失败或功能受限.
我们看一下数据库创建脚本:
====================================================================
CREATE DATABASE "eygle"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/opt/oracle/oradata/eygle/system01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX 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 UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/opt/oracle/oradata/eygle/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 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 10240K
USER 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 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
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.
11G中又加入了新组件。
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: 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
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
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这个改进,分离重要数据及次要数据,分离稳定结构及频繁变化结构,尽量减少对重要数据及结构的影响。
本文转自:http://www.eygle.com/10g/10g_sysaux_tbs.htm
- All about Oracle10g SYSAUX tablespace
- All about oracle smallfile and bigfile tablespace
- Oracle10g新特性-SYSAUX表空间
- About query oracle tablespace
- All about "about face"
- about Undo Tablespace used High ?
- The SYSTEM Tablespace The SYSAUX Tablespace【每日一译】--2012-10-30
- oracle10g的sysaux空间暴增与空间回收
- Oracle10g以上sysaux表空间的维护和清理
- All about abstract classes
- All about love
- All about abstract classes.
- ALL ABOUT .net
- All About JAVA 杂
- All about NFC
- All about parking
- All about Hair Extensions
- All about abstract classes
- hdu 1802 Matrix Chain Multiplication (栈的应用)
- Flex实践——XML文件读取
- 2013年4月23日 腾讯一面之STL中的map实现机制
- Jquery 日期选择
- Cookie & Session总结
- All about Oracle10g SYSAUX tablespace
- CSS 基本语法和高级语法
- HDU 2546
- oracle 远程导入 导出
- 计算几何专项:UVa 10245
- 【ci框架】CI框架中使用cookie的三种方式
- windows下BeautifulSoup学习系列一~~~~安装与引用
- sql server 2005之”SQL server 身份验证“方式登录失败
- 最简单的tab切换