Oracle-SYSAUX表空间解读

SYSAUX概述

官方文档：About the SYSAUX Tablespace

SYSAUX表空间在Oracle Database 10g中引入,作为SYSTEM表空间的辅助表空间.

以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建.

SYSAUX 表空间存放一些其他的 metadata 组件，如 OEM,Streams 等会默认存放在 SYSAUX 表空间里。

通过分离这些组件和功能，SYSTEM表空间的负荷得以减轻.反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免。

因此SYSAUX 表空间也是在在 DB 创建或者升级时自动创建的。 如果在手工使用SQL 创建 DB 时没有指定 SYSAUX tablespace，那么创建语句会报错。 无法执行。

在正常操作下， 不能 drop 和 rename SYSAUX 表空间。

如果SYSAUX表空间不可用,数据库核心功能将保持有效;使用SYSAUX表空间的特点将会失败或功能受限.

我们不能使用 alter tablespace 来修改这 4 个属性，同样也不能 drop 和 rename SYSAUX 表空间。

---

数据库创建脚本中的SYSAUX

我们来看下我们的数据库创建脚本：

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /oracle/admin/cc/scripts/CreateDB.log append
startup nomount pfile="/oracle/admin/cc/scripts/init.ora";
CREATE DATABASE "cc"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oradata/cc/system01.dbf' SIZE 700M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oradata/cc/sysaux01.dbf' SIZE 600M REUSE
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/cc/temp01.dbf' SIZE 20M REUSE
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oradata/cc/undotbs01.dbf' SIZE 200M REUSE
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oradata/cc/redo01.log') SIZE 51200K,
GROUP 2 ('/oradata/cc/redo02.log') SIZE 51200K,
GROUP 3 ('/oradata/cc/redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off

---

SYSAUX表空间的数据库组件

使用SYSAUX表空间的组件	10G以前版本所在表空间
Analytical Workspace Object Table	SYSTEM
Enterprise Manager Repository	OEM_REPOSITORY
LogMiner	SYSTEM
Logical Standby	Logical Standby
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	Statspack Repository
Unified Job Scheduler	New in Oracle Database 10g
Workspace Manager	SYSTEM

---

查看SYSAUX表空间信息-V$SYSAUX_OCCUPANTS

select OCCUPANT_NAME, SCHEMA_NAME,MOVE_PROCEDURE ,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;

说明：

• 这些组件占据这 SYSAUX 表空间，所以这些组件的大小也就决定 SYSAUX表空间的大小。

• schema_name 对应的是用户名。

• 在v$sysaux_occupants 视图里中的move_procudure字段，是用于迁移组件信息的。就是对于已经安装好的组件，如果想把这些组件放到其他的空间，可以使用这个存储过程。如果没有对应的过程，说明不可移动。

比如我们的AWR。 AWR 是SYSAUX 中占用空间最多的组件。 对于一个有10个并发session 的系统，就需要200M 的空间。当然，也可以修改AWR的保存策略来控制AWR所占用空间的大小

---

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

---

栗子：将Logminer 从SYSAUX 表空间，迁移到users表空间，在还原回来

如果希望转移这些系统对象的表空间，可以使用相应得系统包实现:

（1）查看迁移之前的信息：

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR';



OCCUPANT_NAME   OCCUPANT_DESC   SCHEMA_NAME          MOVE_PROCEDURE                           SPACE_USAGE_KBYTES

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

LOGMNR          LogMiner        SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                       6080

（2）调用系统包SYS.DBMS_LOGMNR_D.SET_TABLESPACE迁移

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

PL/SQL procedure successfully completed.

（3）验证迁移后的大小

SQL>  select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR';



OCCUPANT_NAME   OCCUPANT_DESC   SCHEMA_NAME          MOVE_PROCEDURE                           SPACE_USAGE_KBYTES

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

LOGMNR          LogMiner        SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                          0

–注意，这里占空的空间变成了0. 数据迁移到了USERS 表空间

（4）还原到SYSAUX 表空间

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

PL/SQL procedure successfully completed.

（5）验证还原后的大小

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR';



OCCUPANT_NAME   OCCUPANT_DESC   SCHEMA_NAME          MOVE_PROCEDURE                           SPACE_USAGE_KBYTES

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

LOGMNR          LogMiner        SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE                       6080

–观察大小，复位为原来的大小

结论:

这是一个管理及规划上的改进,进一步独立SYSTEM表空间,保证其存储及性能.

我们在做数据库规划时大可借鉴Oracle这个改进,分离重要数据及次要数据，分离稳定结构及频繁变化结构，尽量减少对重要数据及结构的影响。

---