12.15号－－－－－sysaux tablespace

The SYSAUX Tablespace

The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. Therefore, the SYSAUX tablespace is always created during database creation or database upgrade.

The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.

During normal database operation, the Oracle database server does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.

Note:

If the SYSAUX tablespace is unavailable, such as due to a media failure, then some database features might fail.

sysaux表空间是system表空间的辅助表空间。许多组件使用sysaux作为他们默认的本地存储数据，所以，sysaux表空间总是在数据库创建和升级的时候默认被创建。

sysaux表空间为数据元数提供集中的本地存放，而不用存放在system表空间中了。在原始和用户自定义的表空间中，默认降低了表空间的数量。

在正常的数据操作中，数据库不允许sysaux表空间被删除和重命名。

注意：如果sysaux表空间不可用，比如介质的失败，一些数据库的特性会不能用。

我们可以用v$sysaux_occupants 来查看sysau的一些信息，从下面的执行可以看出，sysaux主要提供了logmnr,em,streams等常用的组件。

SQL> desc v$sysaux_occupants;
Name Type Nullable Default Comments
------------------- ------------ -------- ------- --------
OCCUPANT_NAME VARCHAR2(64) Y
OCCUPANT_DESC VARCHAR2(64) Y
SCHEMA_NAME VARCHAR2(64) Y
MOVE_PROCEDURE VARCHAR2(64) Y
MOVE_PROCEDURE_DESC VARCHAR2(64) Y
SPACE_USAGE_KBYTES NUMBER Y

SQL> select occupant_name,schema_name,space_usage_kbytes from v$sysaux_occupants;

OCCUPANT_NAME SCHEMA_NAME SPACE_USAGE_KBYTES
---------------------------------------------------------------- ---------------------------------------------------------------- ------------------
LOGMNR SYSTEM 6080
LOGSTDBY SYSTEM 896
STREAMS SYS 512
XDB XDB 49728
AO SYS 20160
XSOQHIST SYS 20160
XSAMD OLAPSYS 15936
SM/AWR SYS 19008
SM/ADVISOR SYS 4928
SM/OPTSTAT SYS 5888
SM/OTHER SYS 5184
STATSPACK PERFSTAT 0
ODM DMSYS 256
SDO MDSYS 33216
WM WMSYS 7040
ORDIM ORDSYS 512
ORDIM/PLUGINS ORDPLUGINS 0
ORDIM/SQLMM SI_INFORMTN_SCHEMA 0
EM SYSMAN 52160
TEXT CTXSYS 4736

OCCUPANT_NAME SCHEMA_NAME SPACE_USAGE_KBYTES
---------------------------------------------------------------- ---------------------------------------------------------------- ------------------
ULTRASEARCH WKSYS 0
ULTRASEARCH_DEMO_USER WK_TEST 0
EXPRESSION_FILTER EXFSYS 3712
EM_MONITORING_USER DBSNMP 1600
TSM TSMSYS 256
JOB_SCHEDULER SYS 384

26 rows selected

二. 示例：

2.1. 将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）移动

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

--大小又变回来了。

2.2 SYSAUX不能drop

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.3 SYSAUX 不能重命名

SQL> alter tablespace SYSAUX rename to DAVE;

alter tablespace SYSAUX rename to DAVE

*

ERROR at line 1:

ORA-13502: Cannot rename SYSAUX tablespace

2.3 不能将SYSAUX 改成只读

SQL> alter tablesapce SYSAUX read only;

alter tablesapce SYSAUX read only

*

ERROR at line 1:

ORA-00940: invalid ALTER command