SYSAUX表空間在Oracle 10g中引入，其作為SYSTEM表空間的輔助表空間。之前，一些使用獨立表空間或系統表空間的資料庫元件，現在SYSAUX表空間中存在。
透過分離這些元件，減輕了SYSTEM表空間的負荷，避免了因反覆建立一些相關物件及元件引起的SYSTEM表空間碎片問題。SYSAUX 表空間在DB 建立或者升級時自動建立的，如果在手工使用SQL建立DB時沒有指定SYSAUX表空間，那麼，建立語句會報錯，無法執行。
在正常操作下，不能drop 和rename SYSAUX 表空間。如果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 SYSTEM
建立資料庫時指定SYSAUX 表空間，需指定如下4個屬性：
（1）. PERMANENT（永久）
（2）. READ WRITE（可讀寫）
（3）. EXTENT MANAGMENT LOCAL（本地管理）
（4）. SEGMENT SPACE MANAGMENT AUTO（段空間管理自動化）
不能用alter tablespace 來修改這4個屬性，同樣也不能刪除（drop）和重新命名（rename）SYSAUX 表空間。
（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

可以使用v$sysaux_occupants 檢視來檢視SYSAUX 表空間裡的元件資訊：
SQL>set linesize 120 pages 999;
col OCCUPANT_NAME for a30
col SCHEMA_NAME for a20
col MOVE_PROCEDURE for a50
SELECT t.OCCUPANT_NAME,SUM(t.SPACE_USAGE_KBYTES)/1024/1024
FROM gV$SYSAUX_OCCUPANTS t
GROUP BY t.OCCUPANT_NAME
ORDER BY 2 DESC;

可以透過以下SQL分析SYSAUX表空間的使用情況：
WITH
ts_total_space AS (SELECT
TableSpace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks,
SUM(maxbytes) as maxbytes
FROM dba_data_files
GROUP BY TableSpace_name),
ts_free_space AS (SELECT
ddf.TableSpace_name,
NVL(SUM(dfs.bytes),0) as bytes,
NVL(SUM(dfs.blocks),0) as blocks
FROM
dba_data_files ddf,
dba_free_space dfs
WHERE ddf.file_id = dfs.file_id(+)
GROUP BY ddf.TableSpace_name),
ts_total_segments AS (SELECT
TableSpace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks
FROM dba_segments
GROUP BY TableSpace_name),
ts_total_extents AS (SELECT
TableSpace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks
FROM dba_extents
GROUP BY TableSpace_name)
SELECT
dt.TableSpace_name as "TSname",
dt.status as "TSstatus",
ROUND(ttsp.bytes/1024/1024,0) as "TSSizeMb",
ROUND((ttsp.bytes-tfs.bytes)/1024/1024,0) as "TSUsedMb",
ROUND(tfs.bytes/1024/1024,0) as "TSFreeMb",
ROUND((ttsp.bytes-tfs.bytes)/ttsp.bytes*100,0) as "TSUsedPrct",
ROUND(tfs.bytes/ttsp.bytes*100,0) as "TSFreePrct",
ROUND(ttse.bytes/1024/1024,0) as "TSSegUsedMb",
ROUND(tte.bytes/1024/1024,0) as "TSExtUsedMb",
CASE
WHEN ttsp.maxbytes = 0 THEN 'No' ELSE 'Yes'
END as "AutoExtFile",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND(ttsp.maxbytes/1024/1024,0))
END as "TSMaxSizeMb",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.bytes-tfs.bytes)/ttsp.maxbytes*100,0))
END as "TSMaxUsedPrct",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.maxbytes-(ttsp.bytes-tfs.bytes))/ttsp.maxbytes*100,0))
END as "TSMaxFreePrct"
FROM
dba_TableSpaces dt,
ts_total_space ttsp,
ts_free_space tfs,
ts_total_segments ttse,
ts_total_extents tte
WHERE dt.TableSpace_name = ttsp.TableSpace_name(+)
AND dt.TableSpace_name = tfs.TableSpace_name(+)
AND dt.TableSpace_name = ttse.TableSpace_name(+)
AND dt.TableSpace_name = tte.TableSpace_name(+)
AND dt.TableSpace_name = 'SYSAUX';

SQL> desc v$sysaux_occupants
Name Null? Type
----------------------------------------- -------- ----------------------------
OCCUPANT_NAME VARCHAR2(64)
OCCUPANT_DESC VARCHAR2(64)
SCHEMA_NAME VARCHAR2(64)
MOVE_PROCEDURE VARCHAR2(64)
MOVE_PROCEDURE_DESC VARCHAR2(64)
SPACE_USAGE_KBYTES NUMBER
這些元件佔用著SYSAUX 表空間，因此，這些元件的大小也就決定了SYSAUX 表空間的大小。根據這些元件建立時的初始化大小，SYSAUX 表空間至少需要400M的空間。
檢視v$sysaux_occupants裡的schema_name列對應的是元件的使用者名稱。還有個move_procudure列，該列值對應的過程用於遷移元件資訊。對已經安裝好的元件，如想把這些元件放到其他表空間，就可使用這個儲存過程進行遷移。如該列沒有對應的過程，那麼該元件資訊就不可遷移。AWR 是SYSAUX 中佔空間最多的元件。對一個有10個併發session 的系統，就需要200M 的空間。當然，也可以修改AWR的儲存策略來控制AWR佔用空間的大小。
如果希望轉移這些元件資訊的表空間，用相對應的系統包實現即可：
（現以LOGMNR 為例）
SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES ,MOVE_PROCEDURE from v$sysaux_occupants;

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
LOGMNR LogMiner 8064 SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY Logical Standby 1408 SYS.DBMS_LOGSTDBY.SET_TABLESPACE
SMON_SCN_TIME Transaction Layer - SCN to TIME mapping 5376
PL/SCOPE PL/SQL Identifier Collection 1600
STREAMS Oracle Streams 1024
AUDIT_TABLES DB audit tables 0 DBMS_AUDIT_MGMT.move_dbaudit_tables
XDB XDB 130944 XDB.DBMS_XDB.MOVEXDB_TABLESPACE
AO Analytical Workspace Object Table 46208 DBMS_AW.MOVE_AWMETA
XSOQHIST OLAP API History Tables 46208 DBMS_XSOQ.OlapiMoveProc
XSAMD OLAP Catalog 3712 DBMS_AMD.Move_OLAP_Catalog
SM/AWR Server Manageability - Automatic Workload Repository 358464
。。。。。。

31 rows selected.

執行查詢結果中LOGMNR對應的過程：
SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('USERS');

PL/SQL procedure successfully completed.

再次檢視轉移後情況：
SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES ,MOVE_PROCEDURE from v$sysaux_occupants;

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
LOGMNR LogMiner 0 SYS.DBMS_LOGMNR_D.SET_TABLESPACE --注意，這裡佔空的空間變成了0。資訊被遷移到了USERS 表空間
LOGSTDBY Logical Standby 1408 SYS.DBMS_LOGSTDBY.SET_TABLESPACE
。。。。。。
31 rows selected.

再看轉移回來的情況：

SQL> exec SYS.DBMS_LOGMNR_D.SET_TABLESPACE('SYSAUX'); --還原到SYSAUX 表空間

PL/SQL procedure successfully completed.

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES ,MOVE_PROCEDURE from v$sysaux_occupants order by 3;

OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES MOVE_PROCEDURE
------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------------------
LOGMNR LogMiner 8064 SYS.DBMS_LOGMNR_D.SET_TABLESPACE
SM/OTHER Server Manageability - Other Components 8256
。。。。。。
31 rows selected.

透過查詢結果還可以可以看到（為省版面，略去這部分資訊）：AWR報告和OPTSTAT、ADVISOR 佔用空間最大，可以根據具體情況做出相應調整。

（1）修改統計資訊（OPTSTAT）的保持時間，預設為31天，這裡修改為7天，過期的統計資訊會自動被刪除
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> exec dbms_stats.alter_stats_history_retention(7);
PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
7
（2）修改AWR快照的儲存時間為7天(7*24*60),每小時收集一次，也可以透過EM介面檢視和修改，這裡值得注意的是：
（a）RETENTION 引數的取值範圍要求是1天到100年，即快照至少需要保留1天，最長可以保留100年；
（b）INTERVAL 引數的取值範圍要求是10分鐘到100年，即快照採集間隔最少可以是10分鐘，最長可以是100年；
（c）當RETENTION引數被賦予少於1天或超過100年的時候都會給出提示性錯誤ORA-13510；

SQL>col SNAP_INTERVAL for a20
col RETENTION for a20
select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
2727749626 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
--注：
SNAP_INTERVAL=+00000 01:00:00.0 ：表示取樣間隔是1小時
RETENTION=+00008 00:00:00.0 ：表示取樣資料保留期限是8天

SQL> begin
dbms_workload_repository.modify_snapshot_settings (
interval => 60, --每小時一次
retention => 7*24*60, --保留7天
topnsql => 100
);
end;
注意:如果快照太多，佔用太多空間，也可以刪除一些快照：
select min(snap_id),max(snap_id) from dba_hist_snapshot; --查詢最最小和最大快照ID

begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 10758, --根據情況設定
high_snap_id => 10900,
dbid => 387090299); DBID根據實際情況
end;
/
--注：
（1）如果AWR沒有用的話，可以將取樣時間間隔調整為最長100年，同時，把取樣資料保留時間調整到最小1天的方式實現“禁用”AWR。
SQL> BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 36500*24*60,
retention => 1*24*60);
END;
/
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
4134757407 +36500 00:00:00.0 +00001 00:00:00.0 DEFAULT

日常工作中，經常發現SYSTEM和SYSAUX處於高使用率，檢視錶空間使用率SQL如下：
set linesize 200
SELECT df.tablespace_name , (df.totalspace - NVL(fs.freespace, 0)) ,NVL(fs.freespace, 0) ,df.totalspace ,ROUND(100 * (1 - NVL(fs.freespace, 0) / df.totalspace), 2)
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) freespace
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY 5 DESC;

TABLESPACE_NAME (DF.TOTALSPACE-NVL(FS.FREESPACE,0)) NVL(FS.FREESPACE,0) TOTALSPACE ROUND(100*(1-NVL(FS.FREESPACE,0)/DF.TOTALSPACE),2)
------------------------------ ----------------------------------- ------------------- ---------- --------------------------------------------------
SYSAUX 1235 85 1320 93.56
USERS 21762 2252 24014 90.62
PJBJ 73280 23328 196608 88.13
PJBJ_FULL 23905 27295 51200 46.69
SYSTEM 1199 2937 4136 28.99
FULLCAR 7482 23238 30720 24.36
UNDOTBS1 775 27115 27890 2.78
VEHICLE 44 20436 20480 .21

關注表空間資料檔案是否自動擴充套件，方法如下：

SQL> select tablespace_name, AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files where tablespace_name in ('SYSTEM','SYSAUX');

TABLESPACE_NAME AUT INCREMENT_BY
------------------------------ --- ------------
SYSAUX YES 1280
SYSTEM YES 1280
SYSTEM NO 0
AUTOEXTENTSIBLE 對應的值為YES 表示可以自動擴充套件，NO 表示不自動擴充套件，INCREMENT_BY 表示每次自動擴充套件的空間大小。
如果資料檔案不是自動擴充套件的，可使用如下三種方法：

（1 ）增加sysaux表空間的資料檔案
alter tablespace sysaux add datafile “\home\oracle\oradata\ora11g\sysaux_01.dbf' size 1024M;

（2）增加已存在資料檔案大小
alter database datafile '\home\oracle\oradata\ora11g\sysaux_01.dbf' resize 2048M;

（3）修改sysaux表空間，使其自動擴充套件：
alter database datafile '\home\oracle\oradata\ora11g\systaux_01.dbf' autoextend on;

Oracle10g以上sysaux表空間的維護和清理

相關文章