数据库awk未生成

前言
本人在进行数据库巡检时，进行产生awr报告，却发现数据库未查收awr快照，导致不能进食生成awr报告

整个案例分析
使用脚本生成awr报告

@?/rdbms/admin/awrrpt.sql;
输入生成报告的格式：html
输入num_days的值：1
Listing the last 1 days of Completed Snapshots

发现数据库未有awr快照，进行手动生成快照

exec dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

出现报错：
第 1 行出现错误:
ORA-13509: 更新 AWR 表时出错
ORA-01683: 索引 ORA-01683: 索引 SYS.WRH${}_{A}CTIV{E}_{S}ESSIO{N}_{H}ISTOR{Y}_{P}K分区WRH$_ACTIVE_1148453265_0 无法通过 8192 (在表空间 SYSAUX 中) 扩展
. 分区 无法通过 (在表空间 中) 扩展
ORA-06512: 在 “SYS.DBMS_WORKLOAD_REPOSITORY”, line 99
ORA-06512: 在 “SYS.DBMS_WORKLOAD_REPOSITORY”, line 122

分析为数据库表空间不足，查看表空间使用情况

select * from ( 
Select a.tablespace_name, 
to_char(a.bytes/1024/1024,'99,999.999') total_bytes, 
to_char(b.bytes/1024/1024,'99,999.999') free_bytes, 
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes, 
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use 
from (select tablespace_name, 
sum(bytes) bytes 
from dba_data_files 
group by tablespace_name) a, 
(select tablespace_name, 
sum(bytes) bytes 
from dba_free_space 
group by tablespace_name) b 
where a.tablespace_name = b.tablespace_name 
union all 
select c.tablespace_name, 
to_char(c.bytes/1024/1024,'99,999.999') total_bytes, 
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes, 
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes, 
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use 
from 
(select tablespace_name,sum(bytes) bytes 
from dba_temp_files group by tablespace_name) c, 
(select tablespace_name,sum(bytes_cached) bytes_used 
from v$temp_extent_pool group by tablespace_name) d 
where c.tablespace_name = d.tablespace_name 
);

发现表空间使用率情况如下，sysaux使用率过高

select *
       from (select segment_name,
                    segment_type,
                    bytes / 1024 / 1024
               from dba_segments
              where tablespace_name = 'SYSAUX'
              and bytes / 1024 / 1024 >1000
              order by bytes desc);

1）使用dbms_workload_repository.drop_snapshot_range可以删除历史数据，但是太慢了，通过v$session看到执行的SQL是delete

2）手工生成truncate,需要在sys用户下执行

select distinct 'truncate table ' || segment_name || ';',  
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; 

结果如下

进行truncate table “表名”（上面列出的直接复制，执行就行）

执行完成后，查看sysaux空间情况。

select * from ( 
Select a.tablespace_name, 
to_char(a.bytes/1024/1024,'99,999.999') total_bytes, 
to_char(b.bytes/1024/1024,'99,999.999') free_bytes, 
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes, 
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use 
from (select tablespace_name, 
sum(bytes) bytes 
from dba_data_files 
group by tablespace_name) a, 
(select tablespace_name, 
sum(bytes) bytes 
from dba_free_space 
group by tablespace_name) b 
where a.tablespace_name = b.tablespace_name 
union all 
select c.tablespace_name, 
to_char(c.bytes/1024/1024,'99,999.999') total_bytes, 
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes, 
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes, 
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use 
from 
(select tablespace_name,sum(bytes) bytes 
from dba_temp_files group by tablespace_name) c, 
(select tablespace_name,sum(bytes_cached) bytes_used 
from v$temp_extent_pool group by tablespace_name) d 
where c.tablespace_name = d.tablespace_name 
);

再进行手动生成awr快照进行检验

exec dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;

到这来问题就解决了