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表空间的数据库组件:
使用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
