Oracle10g以上sysaux表空间的维护和清理

原创

lhdz__bj 2024-07-23 10:38:04 博主文章分类：Oracle ©著作权

文章标签 oracle sysaux 清理维护管理 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者lhdz__bj的原创作品，请联系作者获取转载授权，否则将追究法律责任

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;