–查询当前rac集群环境表空间使用情况：
SELECT UPPER(F.TABLESPACE_NAME) “表空间名”,
D.TOT_GROOTTE_MB “表空间大小(M)”,
D.TOT_GROOTTE_MB – F.TOTAL_BYTES “已使用空间(M)”,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB – F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
‘990.99’) || ‘%’ “使用比”,
F.TOTAL_BYTES “空闲空间(M)”,
F.MAX_BYTES “最大块(M)”
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) 最大块(M)
1 APEX 2048 216.31 10.56% 1831.69 1831.69
2 AUDIT_DATABASE 1000 325.69 32.57% 674.31 138
3 GSINFO_DATA 341473 60033.81 17.58% 281439.19 3968
4 GSINFO_DATA_PARTITION 5000 10 0.20% 4990 499
5 MGMT_AD4J_TS 200 1.69 0.85% 198.31 198.25
6 MGMT_ECM_DEPOT_TS 660 146.44 22.19% 513.56 499
7 MGMT_TABLESPACE 9250 4749.81 51.35% 4500.19 410
8 SYSAUX 28220 18610.87 65.95% 9609.13 1038
9 SYSTEM 11070 1352.12 12.21% 9717.88 896.94
10 UNDOTBS1 17085 398.5 2.33% 16686.5 3758
11 UNDOTBS2 23506.88 392.57 1.67% 23114.31 3968
12 USERS 37887.5 491.44 1.30% 37396.06 3968
13 ZABBIX 100 1 1.00% 99 99
1.sysaux表空间占用18G物理空间，占用百分比为65.95 剩余空间9.3G
–清理工作1：
1.大多数SYSAUX表空间由SYS模式中的表WRH$_LATCH，WRH$_SYSSTAT和WRH$_PARAMETER的分区使用，可能是分区索引，可能是分区表本身占用过高，以下我们基于
awrinfo.sql将awr的相关信息，包括快照信息、sysaux空间使用、awr组件、ash等信息通通打印出来。
2.具体实施步骤如下：
[oracle@node1 ~]$ locate awrinfo.sql
/u01/app/11.2.0/grid/rdbms/admin/awrinfo.sql
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrinfo.sql
[oracle@node1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 20 13:34:55 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba;
Connected.
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrinfo.sql
This script will report general AWR information~~~~~~~~~~~
Specify the Report File Name~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: /u01/app/oracle/awrinfo.txt
Using the report name /u01/app/oracle/awrinfo.txt
……
……
……
3.分析awrinfo.txt内容，具体改文档内容详见压缩包附件
(1a) SYSAUX usage – Schema breakdown (dba_segments)
|
| Total SYSAUX size 18,597.9 MB ( AUTOEXTEND ON for 1 out of 13 files )
| Fixed limit 5,988.0 MB
| Auto Extent limit 32,768.0 MB
|
| Schema SYS occupies 18,110.5 MB ( 97.4% )
| Schema XDB occupies 369.9 MB ( 2.0% )
| Schema MDSYS occupies 74.3 MB ( 0.4% )
| Schema SYSTEM occupies 14.8 MB ( 0.1% )
| Schema ORDDATA occupies 13.6 MB ( 0.1% )
| Schema OLAPSYS occupies 5.1 MB ( 0.0% )
| Schema WMSYS occupies 3.7 MB ( 0.0% )
| Schema CTXSYS occupies 3.6 MB ( 0.0% )
| Schema DBSNMP occupies 2.0 MB ( 0.0% )
| Schema ORDSYS occupies 0.4 MB ( 0.0% )
1.SYSAUX表空间总占用容量为18,597.9 MB ，SYS用户占用了18,110.5 MB，是整体SYSAUX表空间的97.4%
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
|
| Occupant Name Schema Name Space Usage
| ——————– ——————– —————-
| SM/AWR SYS 16,232.2 MB
| SM/OPTSTAT SYS 737.2 MB
| XDB XDB 369.9 MB
| JOB_SCHEDULER SYS 320.4 MB
| SM/ADVISOR SYS 175.5 MB
| SDO MDSYS 74.3 MB
| AO SYS 38.2 MB
| XSOQHIST SYS 38.2 MB
| ORDIM/ORDDATA ORDDATA 13.6 MB
| LOGMNR SYSTEM 13.4 MB
该指标展现基于v$sysaux_occupants动态性能视图，我们通过指标可以清楚看到SM/AWR占用了16,232.2 MB空间
(3b) Space usage within AWR Components (> 500K)
COMPONENT MB SEGMENT_NAME – % SPACE_USED SEGMENT_TYPE
FIXED 1,016.0 WRH$_LATCH_MISSES_SUMMARY.WRH$_LATCH__2545758487_0 – 21% TABLE PARTITION
FIXED 1,003.9 WRH$_LATCH_MISSES_SUMMARY_PK.WRH$_LATCH__2545758487_0 – 27% INDEX PARTITION
FIXED 920.0 WRH$_LATCH.WRH$_LATCH_2545758487_0 – 12% TABLE PARTITION
FIXED 784.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_2545758487_0 – 17% INDEX PARTITION
FIXED 591.0 WRH$_SYSSTAT.WRH$_SYSSTA_2545758487_0 – 14% TABLE PARTITION
FIXED 563.8 WRH$_LATCH_PK.WRH$_LATCH_2545758487_0 – 18% INDEX PARTITION
FIXED 520.0 WRH$_PARAMETER_PK.WRH$_PARAME_2545758487_0 – 17% INDEX PARTITION
FIXED 432.0 WRH$_PARAMETER.WRH$_PARAME_2545758487_0 – 13% TABLE PARTITION
FIXED 248.0 WRH$_SERVICE_STAT_PK.WRH$_SERVIC_2545758487_0 – 24% INDEX PARTITION
FIXED 128.0 WRH$_ROWCACHE_SUMMARY.WRH$_ROWCAC_2545758487_0 – 18% TABLE PARTITION
FIXED 128.0 WRH$_SERVICE_STAT.WRH$_SERVIC_2545758487_0 – 21% TABLE PARTITION
FIXED 104.0 WRH$_ROWCACHE_SUMMARY_PK.WRH$_ROWCAC_2545758487_0 – 28% INDEX PARTITION
FIXED 104.0 WRH$_MVPARAMETER_PK.WRH$_MVPARA_2545758487_0 – 84% INDEX PARTITION
FIXED 104.0 WRH$_MVPARAMETER.WRH$_MVPARA_2545758487_0 – 98% TABLE PARTITION
FIXED 80.0 WRH$_SERVICE_WAIT_CLASS_PK.WRH$_SERVIC_2545758487_0 – 26% INDEX PARTITION
FIXED 55.0 WRH$_SERVICE_WAIT_CLASS.WRH$_SERVIC_2545758487_0 – 21% TABLE PARTITION
FIXED 47.0 WRH$_DB_CACHE_ADVICE.WRH$_DB_CAC_2545758487_0 – 19% TABLE PARTITION
FIXED 37.0 WRH$_DB_CACHE_ADVICE_PK.WRH$_DB_CAC_2545758487_0 – 22% INDEX PARTITION
FIXED 33.0 WRH$_SYSMETRIC_HISTORY_INDEX – 65% INDEX
……
……
……
基于WRH$_LATCH，WRH$_SYSSTAT和WRH$_PARAMETER动态性能视图，完成如下sql编写工作：
SELECT COUNT(1)
FROM SYS.WRH$_PARAMETER A
WHERE NOT EXISTS (SELECT ‘X’
FROM SYS.WRM$_SNAPSHOT B
WHERE B.SNAP_ID = A.SNAP_ID
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
DELETE FROM SYS.WRH$_LATCH A
WHERE NOT EXISTS (SELECT 1
FROM SYS.WRM$_SNAPSHOT B
WHERE B.SNAP_ID = A.SNAP_ID
AND DBID = (SELECT DBID FROM V$DATABASE)
AND B.DBID = A.DBID
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER);
DELETE FROM SYS.WRH$_SYSSTAT A
WHERE NOT EXISTS (SELECT 1
FROM SYS.WRM$_SNAPSHOT B
WHERE B.SNAP_ID = A.SNAP_ID
AND DBID = (SELECT DBID FROM V$DATABASE)
AND B.DBID = A.DBID
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER);
DELETE FROM SYS.WRH$_PARAMETER A
WHERE NOT EXISTS (SELECT 1
FROM SYS.WRM$_SNAPSHOT B
WHERE B.SNAP_ID = A.SNAP_ID
AND DBID = (SELECT DBID FROM V$DATABASE)
AND B.DBID = A.DBID
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER);
DELETE FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A
WHERE NOT EXISTS (SELECT 1
FROM SYS.WRM$_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND A.DBID = B.DBID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);
如果删除历史活动session速度较慢，可以使用如下脚本
DECLARE
V_COUNT INTEGER;
BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM WRH$_ACTIVE_SESSION_HISTORY A
WHERE NOT EXISTS (SELECT 1
FROM WRM$_SNAPSHOT
WHERE SNAP_ID = A.SNAP_ID
AND DBID = A.DBID
AND INSTANCE_NUMBER = A.INSTANCE_NUMBER)
AND ROWNUM <= 100;
WHILE V_COUNT != 0 LOOP
DELETE FROM WRH$_ACTIVE_SESSION_HISTORY A
WHERE NOT EXISTS (SELECT 1
FROM WRM$_SNAPSHOT
WHERE SNAP_ID = A.SNAP_ID
AND DBID = A.DBID
AND INSTANCE_NUMBER = A.INSTANCE_NUMBER)
AND ROWNUM <= 1000000;
COMMIT;
SELECT COUNT(1)
INTO V_COUNT
FROM WRH$_ACTIVE_SESSION_HISTORY A
WHERE NOT EXISTS (SELECT 1
FROM WRM$_SNAPSHOT
WHERE SNAP_ID = A.SNAP_ID
AND DBID = A.DBID
AND INSTANCE_NUMBER = A.INSTANCE_NUMBER)
AND ROWNUM <= 100;
END LOOP;
END;
–清理工作2
DECLARE
CURSOR CUR_PART IS
SELECT PARTITION_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = ‘WRH$_ACTIVE_SESSION_HISTORY’;
QUERY1 VARCHAR2(200);
QUERY2 VARCHAR2(200);
TYPE PARTREC IS RECORD(
SNAPID NUMBER,
DBID NUMBER);
TYPE PARTLIST IS TABLE OF PARTREC;
OUTLIST PARTLIST;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘PARTITION NAME SNAP_ID DBID’);
DBMS_OUTPUT.PUT_LINE(‘————————— ——- ———-‘);
FOR PART IN CUR_PART LOOP
QUERY1 := ‘select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition (‘ ||
PART.PARTITION_NAME || ‘) group by dbid’;
EXECUTE IMMEDIATE QUERY1 BULK COLLECT
INTO OUTLIST;
IF OUTLIST.COUNT > 0 THEN
FOR I IN OUTLIST.FIRST .. OUTLIST.LAST LOOP
DBMS_OUTPUT.PUT_LINE(PART.PARTITION_NAME || ‘ Min ‘ || OUTLIST(I)
.SNAPID || ‘ ‘ || OUTLIST(I).DBID);
END LOOP;
END IF;
QUERY2 := ‘select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition (‘ ||
PART.PARTITION_NAME || ‘) group by dbid’;
EXECUTE IMMEDIATE QUERY2 BULK COLLECT
INTO OUTLIST;
IF OUTLIST.COUNT > 0 THEN
FOR I IN OUTLIST.FIRST .. OUTLIST.LAST LOOP
DBMS_OUTPUT.PUT_LINE(PART.PARTITION_NAME || ‘ Max ‘ || OUTLIST(I)
.SNAPID || ‘ ‘ || OUTLIST(I).DBID);
DBMS_OUTPUT.PUT_LINE(‘—‘);
END LOOP;
END IF;
END LOOP;
END;
1.以上脚本打印输出内容格式如下：
PARTITION NAME SNAP_ID DBID
WRH$_ACTIVE_2545758487_0 Min 14894 2545758487
WRH$_ACTIVE_2545758487_0 Max 16987 2545758487
2.该脚本主要功能是基于DBA_TAB_PARTITIONS去查找WRH$_ACTIVE_SESSION_HISTORY历史活动会话视图中最大快照ID并打印输出
3.执行如下命令：
BEGIN
Dbms_Workload_Repository.drop_snapshot_range(low_snap_id =>1 ,high_snap_id =>16987 );<———–这步执行会很慢，低峰值操作
END ;
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY SHRINK SPACE CASCADE;
4.注意，数据库低峰值期间操作，drop_snapshot_rang本身速度很慢，因为该API需要去轮训在awrinfo中查询出来的所有WRH$数据字典
5.删除历史统计信息，经过测试逐步删除速度要不直接删除某个时间点速度快，针对个人数据库情况，有条件删除：
exec dbms_stats.purge_stats(sysdate-101);
exec dbms_stats.purge_stats(sysdate-80);
exec dbms_stats.purge_stats(sysdate-51);
exec dbms_stats.purge_stats(sysdate-30);
exec dbms_stats.purge_stats(sysdate-20);
exec dbms_stats.purge_stats(sysdate-5);
–清理工作3
将历史统计信息相关的表进行MOVE并及时进行索引重建
SELECT T.INDEX_NAME,
T.TABLE_OWNER,
T.INDEX_TYPE,
T.TABLE_NAME,
T.STATUS,
T.TABLESPACE_NAME,
TT.NUM_ROWS,
TT.LAST_ANALYZED
FROM DBA_INDEXES T
INNER JOIN DBA_TABLES TT
ON (T.TABLE_OWNER = TT.OWNER AND T.TABLE_NAME = TT.TABLE_NAME)
WHERE T.INDEX_NAME IN (‘I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST’,
‘I_WRI$_OPTSTAT_HH_ST’,
‘I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST’,
‘I_WRI$_OPTSTAT_H_ST’,
‘I_WRI$_OPTSTAT_IND_OBJ#_ST’,
‘I_WRI$_OPTSTAT_IND_ST’,
‘I_WRI$_OPTSTAT_TAB_OBJ#_ST’,
‘I_WRI$_OPTSTAT_TAB_ST’);
alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_HH_ST rebuild online;
alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_H_ST rebuild online;
alter table sys.WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_IND_ST rebuild online;
alter table sys.WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_TAB_ST rebuild online;
ALTER TABLE SYS.WRI$_OPTSTAT_OPR MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRI$_OPTSTAT_AUX_HISTORY MOVE TABLESPACE SYSAUX;
ALTER INDEX SYS.I_WRI$_OPTSTAT_AUX_ST REBUILD ONLINE;
ALTER INDEX SYS.I_WRI$_OPTSTAT_OPR_STIME REBUILD ONLINE;