星空网 > 软件开发 > 数据库

DROP_SNAPSHOT_RANGE过程不能清理表RM$_SNAPSHOT_DETAILS

    今天在测试、验证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>


DROP_SNAPSHOT_RANGE过程不能清理表RM<p>    今天在测试、验证DROP_SNAPSHOT_RANGE不能彻底快照的过程中遇到了DROP_SNAPSHOT_RANGE无法清理WRM$_SNAPSHOT_DETAILS表中数据的情况，测试服务器版本为10.2.0.5.0，AWR的快照是1小时采集一次数据，快照保留14天，也就是二周。具体情况如下所示：</p> <pre > </pre><pre ><a href=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]
Information in this document applies to any platform.

SYMPTOMS

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:

SQL> SELECT MIN(snap_id), max(snap_id) , cast(min(begin_time) as date) "Min Begin Time", CAST(MAX(begin_time) AS DATE) "Max Begin Time", COUNT(*) FROM sys.wrm$_snapshot_details a WHERE NOT EXISTS  (SELECT *  FROM sys.wrm$_snapshot b  WHERE b.snap_id    = a.snap_id  AND a.dbid      = b.dbid  and a.instance_number = b.instance_number )MIN(SNAP_ID) MAX(SNAP_ID) Min Begin Time    Max Begin Time     COUNT(*)------------ ------------ -------------------- -------------------- ----------      1     6993 29-nov-2011 21:00:01 24-sep-2012 22:00:17   577574

 

CAUSE

This issue is caused by an unpublished bug:

Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged

The verification criteria for the bug are:

  1. Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
  2. Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
  3. If snap id's from the range that you chose to drop are still present, then you are hitting this bug.

SOLUTION

The following solutions are available:

  • The Patch 9797851 for unpublished Bug 9797851 is available for some platforms and can be downloaded from My Oracle Support
  • 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掉了，请留意自己的版本信息。




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


DROP_SNAPSHOT_RANGE过程不能清理表RM<p>    今天在测试、验证DROP_SNAPSHOT_RANGE不能彻底快照的过程中遇到了DROP_SNAPSHOT_RANGE无法清理WRM$_SNAPSHOT_DETAILS表中数据的情况，测试服务器版本为10.2.0.5.0，AWR的快照是1小时采集一次数据，快照保留14天，也就是二周。具体情况如下所示：</p> <pre > </pre><pre ><a href=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]
Information in this document applies to any platform.

SYMPTOMS

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:

SQL> SELECT MIN(snap_id), max(snap_id) , cast(min(begin_time) as date) "Min Begin Time", CAST(MAX(begin_time) AS DATE) "Max Begin Time", COUNT(*) FROM sys.wrm$_snapshot_details a WHERE NOT EXISTS  (SELECT *  FROM sys.wrm$_snapshot b  WHERE b.snap_id    = a.snap_id  AND a.dbid      = b.dbid  and a.instance_number = b.instance_number )MIN(SNAP_ID) MAX(SNAP_ID) Min Begin Time    Max Begin Time     COUNT(*)------------ ------------ -------------------- -------------------- ----------      1     6993 29-nov-2011 21:00:01 24-sep-2012 22:00:17   577574

 

CAUSE

This issue is caused by an unpublished bug:

Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged

The verification criteria for the bug are:

  1. Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
  2. Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
  3. If snap id's from the range that you chose to drop are still present, then you are hitting this bug.

SOLUTION

The following solutions are available:

  • The Patch 9797851 for unpublished Bug 9797851 is available for some platforms and can be downloaded from My Oracle Support
  • 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掉了，请留意自己的版本信息。




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]
Information in this document applies to any platform.

SYMPTOMS

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:

SQL> SELECT MIN(snap_id), max(snap_id) , cast(min(begin_time) as date) "Min Begin Time", CAST(MAX(begin_time) AS DATE) "Max Begin Time", COUNT(*) FROM sys.wrm$_snapshot_details a WHERE NOT EXISTS  (SELECT *  FROM sys.wrm$_snapshot b  WHERE b.snap_id    = a.snap_id  AND a.dbid      = b.dbid  and a.instance_number = b.instance_number )MIN(SNAP_ID) MAX(SNAP_ID) Min Begin Time    Max Begin Time     COUNT(*)------------ ------------ -------------------- -------------------- ----------      1     6993 29-nov-2011 21:00:01 24-sep-2012 22:00:17   577574

 

CAUSE

This issue is caused by an unpublished bug:

Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged

The verification criteria for the bug are:

  1. Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
  2. Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
  3. If snap id's from the range that you chose to drop are still present, then you are hitting this bug.

SOLUTION

The following solutions are available:

  • The Patch 9797851 for unpublished Bug 9797851 is available for some platforms and can be downloaded from My Oracle Support
  • 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掉了，请留意自己的版本信息。

DROP_SNAPSHOT_RANGE过程不能清理表RM<p>    今天在测试、验证DROP_SNAPSHOT_RANGE不能彻底快照的过程中遇到了DROP_SNAPSHOT_RANGE无法清理WRM$_SNAPSHOT_DETAILS表中数据的情况，测试服务器版本为10.2.0.5.0，AWR的快照是1小时采集一次数据，快照保留14天，也就是二周。具体情况如下所示：</p> <pre > </pre><pre ><a href=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]
Information in this document applies to any platform.

SYMPTOMS

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:

SQL> SELECT MIN(snap_id), max(snap_id) , cast(min(begin_time) as date) "Min Begin Time", CAST(MAX(begin_time) AS DATE) "Max Begin Time", COUNT(*) FROM sys.wrm$_snapshot_details a WHERE NOT EXISTS  (SELECT *  FROM sys.wrm$_snapshot b  WHERE b.snap_id    = a.snap_id  AND a.dbid      = b.dbid  and a.instance_number = b.instance_number )MIN(SNAP_ID) MAX(SNAP_ID) Min Begin Time    Max Begin Time     COUNT(*)------------ ------------ -------------------- -------------------- ----------      1     6993 29-nov-2011 21:00:01 24-sep-2012 22:00:17   577574

 

CAUSE

This issue is caused by an unpublished bug:

Document 9797851.8 Bug 9797851 - WRM$_SNAPHOST_DETAILS is never purged

The verification criteria for the bug are:

  1. Drop a range of snap id's using dbms_workload_repository.drop_snapshot_range()
  2. Check the corresponding snap id's in WRM$_SNAPSHOT_DETAILS.
  3. If snap id's from the range that you chose to drop are still present, then you are hitting this bug.

SOLUTION

The following solutions are available:

  • The Patch 9797851 for unpublished Bug 9797851 is available for some platforms and can be downloaded from My Oracle Support
  • 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掉了，请留意自己的版本信息。




SNAPSHOT_DETAILS' title="image" src='/images/loading.gif' data-original="http://images2015.cnblogs.com/blog/73542/201606/73542-20160630231107577-2041649451.png" width="703" height="614">




原标题：DROP_SNAPSHOT_RANGE过程不能清理表RM$_SNAPSHOT_DETAILS

关键词：

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们： admin#shaoqun.com （#换成@）。
相关文章
我的浏览记录
最新相关资讯
跨境电商 软件开发 营销推广 产品运营 短视频运营

Copyright © 星空网

海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流