----0、表空间使用率
col TABLESPACE_NAME for a20;
col PCT_FREE for a10;
col PCT_USED for a10;
set lines 200;
Select Tablespace_Name,
Sum_m,
Max_m,
Count_Blocks Free_Blk_Cnt,
Sum_Free_m,
To_Char(100 * Sum_Free_m / Sum_m, '99.9999')|| '%' As Pct_Free,
100 - To_Char(100 * Sum_Free_m / Sum_m,'99.9999') || '%' As Pct_used
From (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 As Sum_m
From Dba_Data_Files
Group By Tablespace_Name)
Left Join
(Select Tablespace_Name As Fs_Ts_Name,
Max(Bytes) / 1024 / 1024 As Max_m,
Count(Blocks) As Count_Blocks,
Sum(Bytes / 1024 / 1024) As Sum_Free_m
From Dba_Free_Space
Group By Tablespace_Name)
On Tablespace_Name = Fs_Ts_Name
ORDER BY Sum_Free_m / Sum_m ;
----1、查看AWR保存策略
col SNAP_INTERVAL format a20
col RETENTION format a20
select * from dba_hist_wr_control;
----2、查看AWR占用空间
col Item for a30
col Schema for a20
set lines 200
SELECT occupant_name"Item",
round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
schema_name "Schema",
move_procedure "MoveProcedure"
FROM v$sysaux_occupants
ORDER BY 2 Desc;
----3、查看占用SYSAUX表空间的segment
--all
SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_M
FROM DBA_SEGMENTS D
WHERE D.TABLESPACE_NAME = 'SYSAUX'
GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE ORDER BY SIZE_M DESC;
--前10
SELECT * FROM (SELECT SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
BYTES / 1024 / 1024
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSAUX'
ORDER BY 4 DESC)
WHERE ROWNUM <= 10;
----4、查询占用
col trun_table for a50;
select distinct 'truncate table '||segment_name||';' trun_table ,s.bytes/1024/1024
from dba_segments s
where s.segment_name like 'WRH$%'
and segment_type in ('TABLE PARTITION', 'TABLE')
and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;
----5、查看AWR真正保留时长
set lines 200
col ASH for a40;
col SNAP for a40;
col RETENTION for a40;
select sysdate - a.sample_time ash,
sysdate - s.begin_interval_time snap,
c.RETENTION
from sys.wrm$_wr_control c,
(
select db.dbid,min(w.SAMPLE_time) SAMPLE_time
from sys.v_$database db,sys.wrh$_active_session_history w
where w.dbid = db.dbid
group by db.dbid
) a,
(
select db.dbid,min(r.begin_interval_time) begin_interval_time
from sys.v_$database db,sys.wrm$_snapshot r
where r.dbid = db.dbid
group by db.dbid
) s
where a.dbid = s.dbid
and c.dbid = a.dbid;
ASH SNAP RETENTION
---------------------------------------- ---------------------------------------- ----------------------------------------
+000001454 18:11:15.512 +000000002 18:35:02.485 +00008 00:00:00.0
----6、查看AWR快照未清理原因
set lines 200
col SEGMENT_NAME for a40;
col PARTITION_NAME for a30;
col SEGMENT_TYPE for a30;
col OWNER for a10;
select owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
from DBA_SEGMENTS
where segment_name='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$_EVENT_HISTOGRAM';
WRH$_ACTIVE_SESSION_HISTORY由于分区失败，导致所有数据均在同一个表空间，所以删除过期快照失败。
----7、处理步骤
1）查看未拆分的分区
Check the partition details for the offending table before the split:
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_1099728244_0 TABLE PARTITION 13.5166016
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
2）使用Oracle命令对AWR所有表进行分区
Split the partitions so that there is more chance of the smaller partition being purged:
alter session set "_swrf_test_action" = 72;
NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.
3）检查新的分区情况
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_1099728244_0 TABLE PARTITION 13.5166016
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1099728244_56761 TABLE PARTITION .000061035
SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
4）删除老的分区
以上步骤执行完成后，再truncate原来的分区，只保留新分区，释放空间
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY TRUNCATE PARTITION WRH$_ACTIVE_1099728244_0;
5）检查索引是否正常
select index_name,partition_name,status from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
select index_name,partition_name,status from dba_ind_partitions where index_name='WRH$_EVENT_HISTOGRAM_PK';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1099728244_0 USABLE
WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1099728244_56761 USABLE
WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN USABLE
6）手动收集AWR验证
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();