今天在测试、验证DROP_SNAPSHOT_RANGE不能彻底快照的过程中遇到了DROP_SNAPSHOT_RANGE无法清理WRM$_SNAPSHOT_DETAILS表中数据的情况,测试服务器版本为10.2.0.5.0,AWR的快照是1小时采集一次数据,快照保留14天,也就是二 ...
今天在测试、验证DROP_SNAPSHOT_RANGE不能彻底快照的过程中遇到了DROP_SNAPSHOT_RANGE无法清理WRM$_SNAPSHOT_DETAILS表中数据的情况,测试服务器版本为10.2.0.5.0,AWR的快照是1小时采集一次数据,快照保留14天,也就是二周。具体情况如下所示:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> COL SNAP_INTERVAL FOR A20;
SQL> COL TETENTION FOR A26;
SQL> SELECT * FROM dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- --------------------------- ----------
3990839260 +00000 01:00:00.0 +00014 00:00:00.0 DEFAULT
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59195
SQL>
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> COL SNAP_INTERVAL FOR A20;
SQL> COL TETENTION FOR A26;
SQL> SELECT * FROM dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- --------------------------- ----------
3990839260 +00000 01:00:00.0 +00014 00:00:00.0 DEFAULT
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59195
SQL>
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59196
SQL> select dbid, status, count(*)
2 from wrm$_snapshot
3 group by dbid, status;
DBID STATUS COUNT(*)
---------- ---------- ----------
3990839260 0 1250
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59196 3990839260
SQL> exec dbms_workload_repository.drop_snapshot_range(7417,59196,3990839260);
PL/SQL procedure successfully completed.
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59197
如上实验所示,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';
"select snap_id, dbid, instance_number, startup_time,
begin_interval_time, end_interval_time,
flush_elapsed, snap_level, error_count
from WRM$_SNAPSHOT
where status = 0"
其实这个是一个Bug引起的,官方文档WRM$_SNAPSHOT_DETAILS Table is Not Purged (文档 ID 1489801.1) 和文档 Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged 都有描述这个Bug
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
SNAPSHOT_DETAILS' title="clip_image001" src='/images/loading.gif' data-original="http://images2015.cnblogs.com/blog/73542/201606/73542-20160630231103937-1655440877.png" width="650" height="273">
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59196
SQL> select dbid, status, count(*)
2 from wrm$_snapshot
3 group by dbid, status;
DBID STATUS COUNT(*)
---------- ---------- ----------
3990839260 0 1250
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59196 3990839260
SQL> exec dbms_workload_repository.drop_snapshot_range(7417,59196,3990839260);
PL/SQL procedure successfully completed.
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59197
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> COL SNAP_INTERVAL FOR A20;
SQL> COL TETENTION FOR A26;
SQL> SELECT * FROM dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- --------------------------- ----------
3990839260 +00000 01:00:00.0 +00014 00:00:00.0 DEFAULT
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59195
SQL>
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59196
SQL> select dbid, status, count(*)
2 from wrm$_snapshot
3 group by dbid, status;
DBID STATUS COUNT(*)
---------- ---------- ----------
3990839260 0 1250
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59196 3990839260
SQL> exec dbms_workload_repository.drop_snapshot_range(7417,59196,3990839260);
PL/SQL procedure successfully completed.
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> select min(snap_id), max(snap_id), dbid from wrm$_snapshot
2 group by dbid;
MIN(SNAP_ID) MAX(SNAP_ID) DBID
------------ ------------ ----------
7417 59197 3990839260
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
7417 59197
如上实验所示,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';
"select snap_id, dbid, instance_number, startup_time,
begin_interval_time, end_interval_time,
flush_elapsed, snap_level, error_count
from WRM$_SNAPSHOT
where status = 0"
其实这个是一个Bug引起的,官方文档WRM$_SNAPSHOT_DETAILS Table is Not Purged (文档 ID 1489801.1) 和文档 Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged 都有描述这个Bug
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
SNAPSHOT_DETAILS' title="clip_image003" src='/images/loading.gif' data-original="http://images2015.cnblogs.com/blog/73542/201606/73542-20160630231106140-285229131.png" width="650" height="480">
如上实验所示,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';
"select snap_id, dbid, instance_number, startup_time,
begin_interval_time, end_interval_time,
flush_elapsed, snap_level, error_count
from WRM$_SNAPSHOT
where status = 0"
其实这个是一个Bug引起的,官方文档WRM$_SNAPSHOT_DETAILS Table is Not Purged (文档 ID 1489801.1) 和文档 Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged 都有描述这个Bug
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理:https://www.xlkjsw.com
原标题:DROP_SNAPSHOT_RANGE过程不能清理表RM$_SNAPSHOT_DETAILS
关键词:
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。