awr清理

Oracle并没有真正进行删除AWR报告信息，查询metalink发现是Oracle的bug，
Bug 8622802描述了这个问题，
修复版本：

#禁用awr报告
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL
=> 0);
select * from WRM$_SNAPSHOT_DETAILS

#清理-WRH$_SNAPSHOT_DETAILS

CREATE TABLE SYS.WRM$_SNAPSHOT_DETAILS_bak
TABLESPACE SYSAUX AS
SELECT * FROM SYS.WRM$_SNAPSHOT_DETAILS where 1=2;

INSERT /*+ APPEND */ INTO SYS.WRM$_SNAPSHOT_DETAILS_bak SELECT * FROM
SYS.WRM$_SNAPSHOT_DETAILS_bak WHERE (DBID,SNAP_ID) IN (SELECT
DBID,SNAP_ID FROM DBA_HIST_SNAPSHOT);

commit;

TRUNCATE TABLE SYS.WRM$_SNAPSHOT_DETAILS;
INSERT /*+ APPEND */ INTO SYS.WRM$_SNAPSHOT_DETAILS SELECT * FROM
SYS.WRM$_SNAPSHOT_DETAILS_bak;

COMMIT;

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL
=> 60);

DROP TABLE SYS.WRM$_SNAPSHOT_DETAILS_bak purge;

#清理awr 报告

call DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(low_snap_id => 96,
high_snap_id => 96, dbid => 1160732652);

#设置awr保留时间： retention => 0 永久保留

EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 60, retention => 0);

#设置awr保留时间： retention => 8 保留8天 interval => 60 按1小时收集一次

EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
interval => 60, retention => 8*1440);

#查看设置

select * from dba_hist_wr_control;

查看最大snap_id 与最小snap_id 及对应时间：

select min(snap_id),max(snap_id) from dba_hist_snapshot;
IN(SNAP_ID) MAX(SNAP_ID)
33659 33804
select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from
dba_hist_snapshot where SNAP_ID=33659