The snapshot was not completely removed when awr imported snapshot was deleted

An attempt was taken to remove the snapshot from the Awr import and found that the snapshot was not completely removed.

A dbms_workload_repository executed in a 10.2.0.3 environment. Drop_snapshot_range process, Oracle quickly returned the result, feeling as if Oracle did not really delete, because this part of the snapshot was imported for nearly one hours:

Sql> select * from V$version;

BANNER

----------------------------------------------------------------

Oracle database10genterpriseedition Release10.2.0.3.0-64bi

Pl/sql Release 10.2.0.3.0-production

CORE 10.2.0.3.0 Production

TNS for Linux:version 10.2.0.3.0-production

Nlsrtl Version 10.2.0.3.0-production

sql> Select min (snap_id), Max (snap_id)

2 from Dba_hist_snapshot

3 where dbid = 3944144691;

MIN (snap_id) MAX (snap_id)

------------ ------------

45189 47802

sql> exec dbms_workload_repository.drop_snapshot_range (45189, 47802, 3944144691)

Pl/sql procedure successfully completed.

Sql> SELECT * from dba_hist_snapshot where dbid = 3944144691;

No rows selected

If you examine the data dictionary, Oracle only modifies the state of the corresponding snapshot and does not delete the snapshot:

Sql> select dbid, Status, COUNT (*)

2 from Wrm$_snapshot

3 GROUP by dbid, status;

DBID STATUS COUNT (*)

---------- ---------- ----------

3812548755 0 89

96312462 0 50

3944144691 2 2614

The query Metalink found that Oracle's Bug,bug 8622802 describes the issue, confirming that the affected version includes 10.2.0.3, 10.2.0.4. Oracle fixes this problem in version 12, 11.2.0.2, and 10.2.0.5.

This problem also exists in the test found in 11.2:

Sql> select * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

sql> Select min (snap_id), Max (snap_id)

2 from Dba_hist_snapshot

3 where dbid = 3944144691;

MIN (snap_id) MAX (snap_id)

------------ ------------

45189 47802

sql> exec dbms_workload_repository.drop_snapshot_range (45189, 47802, 3944144691)

The PL/SQL process has completed successfully.

Sql> select dbid, Status, COUNT (*)

2 from Wrm$_snapshot

3 GROUP by dbid, status;

DBID STATUS COUNT (*)

---------- ---------- ----------

4284478783 0 197

3944144691 2 2614

A more serious effect of this error is that the footprint is not freed, and if, like the current example, you intend to delete all snapshots of the specified database, you can use the following procedure:

Sql> select dbid, Status, COUNT (*)

2 from Wrm$_snapshot

3 GROUP by dbid, status;

DBID STATUS COUNT (*)

---------- ---------- ----------

3812548755 0 94

96312462 0 50

3944144691 2 2614

sql> exec dbms_swrf_internal.unregister_database (3944144691)

Pl/sql procedure successfully completed.

Sql> select dbid, Status, COUNT (*)

2 from Wrm$_snapshot

3 GROUP by dbid, status;

DBID STATUS COUNT (*)

---------- ---------- ----------

3812548755 0 94

96312462 0 50