今天在測試、驗證DROP_SNAPSHOT_RANGE不能徹底快照的過程中遇到了DROP_SNAPSHOT_RANGE無法清理WRM$_SNAPSHOT_DETAILS表中資料的情況，測試伺服器版本為10.2.0.4.0，AWR的快照是1小時采集一次資料，快照保留14天，也就是二周。具體情況如下所示：
PL/SQL procedure successfully completed.
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722103801794-1713507451.png"></a>
如上實驗所示，DROP_SNAPSHOT_RANGE不能清理WRM$_SNAPSHOT_DETAILS中的資料，當然對于的空間就不會釋放，另外，有些版本中Oracle僅僅修改了對應SNAPSHOT的狀态，而并沒有删除快照。PS:有些人可能被上面又是DBA_HIST_SNAPSHOT，又是WRM$_SNAPSHOT_DETAILS弄得有點暈，其實DBA_HIST_SNAPSHOT是視圖,它的資料來源于表WRM$_SNAPSHOT_DETAILS，使用下面SQL就能檢視具體定義
SELECT OWNER, VIEW_NAME, TEXT FROM DBA_VIEWS WHERE VIEW_NAME='DBA_HIST_SNAPSHOT';
-------------------------------------------------------------分割線------------------------------------------------------
本來這篇文章寫了好幾天了，後面讨論發現其實有時候AWR快照不能删除，并不一定就是bug，也有可能是設定了AWR的基線，下面我來示範一下
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722103802732-649354079.png"></a>
AWR快照SNAP_ID從7455 到7458 删除不掉，其實是因為這個段的快照設定成了基線，如下截圖所示，是以，如果你發現快照删除不了的話，最好先檢查這個SNAP_ID段是否設定成了基線。
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722103803576-547869485.png"></a>
另外還有就是有可能一個Bug引起的，這個隻出現在特定版本中，官方文檔WRM$_SNAPSHOT_DETAILS Table is Not Purged (文檔 ID 1489801.1) 和文檔 Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged 都有描述這個Bug
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
Information in this document applies to any platform.
The following symptoms are observed:
AWR purge code is not automatically purging WRM$_SNAPSHOT_DETAILS, as expected
Even after dropping a range of snap id's using dbms_workload_repository.drop_snapshot_range(), the table is not purged.
Table WRM$_SNAPSHOT_DETAILS grows indefinitely.
There are many orphaned entries in the table WRM$_SNAPSHOT_DETAILS.
The number of orphaned rows for the table WRM$_SNAPSHOT_DETAILS can be found by running the following sql:
This issue is caused by an unpublished bug:
The verification criteria for the bug are:
Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
If snap id's from the range that you chose to drop are still present, then you are hitting this bug.
The following solutions are available:
If the patch is not available on your platform on a supported version, please contact Oracle Support.
This issue will be fixed from release Oracle 12.1
As a workaround, it is possible to manually purge the range of snap id's from the table WRM$_SNAPSHOT_DETAILS using appropriate delete statments under the guidance of Oracle Support.
Note:
在下面版本中，這些bug才fix掉了，請留意自己的版本資訊。
