2
清理SYSAUX表空间.txt
10墨值下载
清理核心 SYSAUX 表空间
一、检查 SYSAUX 表空间使用情况；如下：
1.当前空间总大小大约 180G 左右，日增大约 350M 左右；
TABLESPACE_NAME SIZE_USED_MB DATA_SIZE_MB FREE_RATE MAX_SIZE_MB
FREE_MB MAX_FREE_RATE AVG_USED_PER_DAY_MB TBS_EXHAUST_DAYS
------------------------------ ------------ ------------ ---------- -----------
---------- ------------- ------------------- ----------------
SYSAUX 186005 217087 14 217087
31082 14 343.3 91
2.检查 SYSAUX 中个部分大小，发现 SM/AWR 这一部分占了 95%以上的空间；如下：
set lines 9999 pages 999
col Item for a30
col schema for a20
col Move_Procedure for a30
SELECT occupant_name "Item",
space_usage_kbytes / 1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1;
Item Space Used (GB) Schema Move
Procedure
------------------------------ --------------- --------------------
------------------------------------
AO .035461426 SYS
DBMS_AW.MOVE_AWMETA
AUDIT_TABLES 0 SYS
DBMS_AUDIT_MGMT.move_dbaudit_tables
AUTO_TASK .000305176 SYS
EM .044921875 SYSMAN
emd_maintenance.move_em_tblspc
EM_MONITORING_USER .001525879 DBSNMP
EXPRESSION_FILTER .003540039 EXFSYS
JOB_SCHEDULER .001037598 SYS
LOGMNR .011962891 SYSTEM
SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY .001342773 SYSTEM
SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM .000427246 ORDSYS
ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .013183594 ORDDATA
ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS 0 ORDPLUGINS
ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA
ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .001525879 SYS
SDO .07220459 MDSYS
MDSYS.MOVE_SDO
SM/ADVISOR .449890137 SYS
SM/AWR 179.906677 SYS
SM/OPTSTAT .797363281 SYS
SM/OTHER .011291504 SYS
SMON_SCN_TIME .014282227 SYS
SQL_MANAGEMENT_BASE .002868652 SYS
STATSPACK 0 PERFSTAT
STREAMS .000976563 SYS
TEXT .003540039 CTXSYS
DRI_MOVE_CTXSYS
TSM 0 TSMSYS
ULTRASEARCH 0 WKSYS MOVE_WK
ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK
WM .003417969 WMSYS
DBMS_WM.move_proc
XDB .154663086 XDB
XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD .005004883 OLAPSYS
DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .035461426 SYS
DBMS_XSOQ.OlapiMoveProc
31 rows selected.
3.查看 SYSAUX 表空间的段大小，按大小排序；发现 WRH$_开头的表占用了大量空间，其中又以
WRH$_ACTIVE_SESSION_HISTORY 最为明显，高达 54G，其对应的主键索引也有 8G，如下：
select segment_name, sum(bytes) / 1024 / 1024
from dba_segments
where tablespace_name = 'SYSAUX'
group by segment_name
order by 2;
...
WRH$_PARAMETER 2549.0625
WRH$_SYSTEM_EVENT 2563.0625
WRH$_SYSSTAT 2824.0625
WRM$_SNAPSHOT_DETAILS_INDEX 3048
WRH$_SEG_STAT 3080.0625
WRH$_LATCH_PK 3400.0625
WRH$_SYSSTAT_PK 3861.0625
WRH$_LATCH 4481.0625
WRH$_FILESTATXS_PK 5294.0625
WRH$_SQLSTAT 5515.0625
WRH$_LATCH_MISSES_SUMMARY 6641.0625
WRH$_LATCH_MISSES_SUMMARY_PK 7865.0625
WRH$_ACTIVE_SESSION_HISTORY_PK 8000.0625
WRH$_FILESTATXS 12527.0625
WRH$_EVENT_HISTOGRAM 12745.0625
WRH$_EVENT_HISTOGRAM_PK 17366.0625
WRH$_ACTIVE_SESSION_HISTORY 54894.0625
4411 rows selected.
4.查询 WRH$_ACTIVE_SESSION_HISTORY 的分区信息；如下：
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
OWNER SEGMENT_NAME PARTITION_NAME
SEGMENT_TYPE SIZE_GB
-------------------- ------------------------------
------------------------------ -------------------- ----------
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
TABLE PARTITION .000061035
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_922980069_186899
TABLE PARTITION 53.5371094
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_922980069_258299
TABLE PARTITION .0703125
of 5
10墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论