STATSPACK清除資料時沒有刪除STATS$UNDOSTAT
Oracle在清除STATSPACK資料的時候，漏掉了STATS$UNDOSTAT表。
無論是刪除STATS$SNAPSHOT表的時候，還是使用SPPURGE.SQL指令碼，都無法清除這張表的記錄。
SQL> SELECT SNAP_ID FROM STATS$SNAPSHOT;
SNAP_ID
----------
1
SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;
COUNT(*)
----------
55
SQL> SELECT COUNT(*) FROM STATS$SGASTAT;
COUNT(*)
----------
41
SQL> DELETE STATS$SNAPSHOT;
已刪除 1 行。
SQL> SELECT COUNT(*) FROM STATS$SGASTAT;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;
COUNT(*)
----------
55
使用SPPURGE.SQL指令碼也是一樣：
SQL> ROLLBACK;
回退已完成。
SQL> @?/rdbms/admin/sppurge.sql
SQL> Rem
SQL> Rem $Header: sppurge.sql 20-mar-2002.18:02:43 vbarrier Exp $
SQL> Rem
SQL> Rem sppurge.sql
SQL> Rem
SQL> Rem Copyright (c) 2000, 2002, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem sppurge.sql - STATSPACK Purge
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem Purge a range of Snapshot Id's between the specified
SQL> Rem begin and end Snap Id's
SQL> Rem
SQL> Rem NOTES
SQL> Rem Should be run as STATSPACK user, PERFSTAT.
SQL> Rem
SQL> Rem Running purge may require the use of a large rollback
SQL> Rem segment; to avoid rollback segment related errors
SQL> Rem explicitly specify a large rollback segment before running
SQL> Rem this script. by using the 'set transaction use rollback segment..'
SQL> Rem command, or alternatively specify a smaller range of
SQL> Rem Snapshot Id's to purge.
SQL> Rem
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem vbarrier 03/20/02 - Optional stats$seg_stat_obj purge
SQL> Rem cdialeri 04/12/01 - 9.0
SQL> Rem cdialeri 04/11/00 - 1261813
SQL> Rem cdialeri 03/15/00 - Conform. to new structure
SQL> Rem densor.uk 05/00/94 - Allow purge of range of snaps
SQL> Rem gwood.uk 10/12/92 - Use RI for deletes to most tables
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set feedback off verify off pages 999
SQL> undefine dbid inst_num losnapid hisnapid
SQL> whenever sqlerror exit rollback
SQL>
SQL> spool sppurge.lis
SQL>
SQL>
SQL> /* ------------------------------------------------------------------------- */
SQL>
SQL> --
SQL> -- Get the current database/instance information - this will be used
SQL> -- later in the report along with bid, eid to lookup snapshots
SQL>
SQL> prompt
SQL> prompt
SQL> prompt Database Instance currently connected to
Database Instance currently connected to
SQL> prompt ========================================
========================================
SQL>
SQL> column inst_num heading "Inst Num" new_value inst_num format 99999;
SQL> column inst_name heading "Instance|Name" new_value inst_name format a10;
SQL> column db_name heading "DB Name" new_value db_name format a10;
SQL> column dbid heading "DB Id" new_value dbid format 9999999999 just c;
SQL> select d.dbid dbid
2 , d.name db_name
3 , i.instance_number inst_num
4 , i.instance_name inst_name
5 from v$database d,
6 v$instance i;
Instance
DB Id DB Name Inst Num Name
----------- ---------- -------- ----------
2270762593 TESTDATA 1 testdata
SQL>
SQL> variable dbid number;
SQL> variable inst_num number;
SQL> variable inst_name varchar2(20);
SQL> variable db_name varchar2(20);
SQL> begin
2 :dbid := &dbid;
3 :inst_num := &inst_num;
4 :inst_name := '&inst_name';
5 :db_name := '&db_name';
6 end;
7 /
SQL>
SQL>
SQL> --
SQL> -- List Snapshots
SQL>
SQL> column snap_id format 9999990 heading 'Snap Id'
SQL> column snap_date format a21 heading 'Snapshot Started'
SQL> column host_name format a15 heading 'Host'
SQL> column parallel format a3 heading 'OPS' trunc
SQL> column level format 99 heading 'Snap|Level'
SQL> column versn format a7 heading 'Release'
SQL> column ucomment heading 'Comment' format a25;
SQL>
SQL> prompt
SQL> prompt
SQL> prompt Snapshots for this database instance
Snapshots for this database instance
SQL> prompt ====================================
====================================
SQL>
SQL> select s.snap_id
2 , s.snap_level "level"
3 , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss') snap_date
4 , di.host_name host_name
5 , s.ucomment
6 from stats$snapshot s
7 , stats$database_instance di
8 where s.dbid = :dbid
9 and di.dbid = :dbid
10 and s.instance_number = :inst_num
11 and di.instance_number = :inst_num
12 and di.startup_time = s.startup_time
13 order by db_name, instance_name, snap_id;
Snap
Snap Id Level Snapshot Started Host Comment
-------- ----- --------------------- --------------- -------------------------
1 5 07 7月 2008 16:35:4 localhost.local
1 domain
SQL>
SQL>
SQL>
SQL> --
SQL> -- Post warning
SQL>
SQL> prompt
SQL> prompt
SQL> prompt Warning
Warning
SQL> prompt ~~~~~~~
~~~~~~~
SQL> prompt sppurge.sql deletes all snapshots ranging between the lower and
sppurge.sql deletes all snapshots ranging between the lower and
SQL> prompt upper bound Snapshot Id's specified, for the database instance
upper bound Snapshot Id's specified, for the database instance
SQL> prompt you are connected to.
you are connected to.
SQL> prompt
SQL> prompt You may wish to export this data before continuing.
You may wish to export this data before continuing.
SQL> prompt
SQL>
SQL>
SQL>
SQL> --
SQL> -- Obtain snapshot ranges
SQL>
SQL> prompt
SQL> prompt Specify the Lo Snap Id and Hi Snap Id range to purge
Specify the Lo Snap Id and Hi Snap Id range to purge
SQL> prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> prompt Using &&LoSnapId for lower bound.
輸入 losnapid 的值: 1
Using 1 for lower bound.
SQL> prompt
SQL> prompt Using &&HiSnapId for upper bound.
輸入 hisnapid 的值: 1
Using 1 for upper bound.
SQL>
SQL>
SQL> variable lo_snap number;
SQL> variable hi_snap number;
SQL> begin
2 :lo_snap := &losnapid;
3 :hi_snap := &hisnapid;
4 end;
5 /
SQL>
SQL> set termout off
SQL>
SQL> set heading off
SQL>
SQL> select 'WARNING: LoSnapId or HiSnapId specified does not exist in STATS$SNAPSHOT'
2 from dual
3 where not exists
4 (select null
5 from stats$snapshot
6 where instance_number = :inst_num
7 and dbid = :dbid
8 and snap_id = :lo_snap)
9 or not exists
10 (select null
11 from stats$snapshot
12 where instance_number = :inst_num
13 and dbid = :dbid
14 and snap_id = :hi_snap);
SQL>
SQL> set heading on
SQL>
SQL>
SQL>
SQL> --
SQL> -- Delete all data for the specified ranges
SQL>
SQL> /* Use RI to delete parent snapshot and all child records */
SQL>
SQL> prompt
SQL> prompt Deleting snapshots &&losnapid - &&hisnapid..
Deleting snapshots 1 - 1.
SQL> delete from stats$snapshot
2 where instance_number = :inst_num
3 and dbid = :dbid
4 and snap_id between :lo_snap and :hi_snap;
SQL>
SQL>
SQL> set termout off;
SQL>
SQL>
SQL>
SQL> /* Delete any undostat rows that cover the snap times */
SQL>
SQL> delete from stats$undostat us
2 where dbid = :dbid
3 and instance_number = :inst_num
4 and begin_time 5 and end_time > to_date(:etime, 'YYYYMMDD HH24:MI:SS');
SQL>
SQL>
SQL>
SQL> /* Delete any dangling database instance rows for that startup time */
SQL>
SQL> delete from stats$database_instance di
2 where instance_number = :inst_num
3 and dbid = :dbid
4 and not exists (select 1
5 from stats$snapshot s
6 where s.dbid = di.dbid
7 and s.instance_number = di.instance_number
8 and s.startup_time = di.startup_time);
SQL>
SQL>
SQL>
SQL> /* Delete any dangling statspack parameter rows for the database instance */
SQL>
SQL> delete from stats$statspack_parameter sp
2 where instance_number = :inst_num
3 and dbid = :dbid
4 and not exists (select 1
5 from stats$snapshot s
6 where s.dbid = sp.dbid
7 and s.instance_number = sp.instance_number);
SQL>
SQL>
SQL> --
SQL> --
SQL>
SQL> prompt
SQL> prompt
SQL> prompt Purge of specified Snapshot range complete. If you wish to ROLLBACK
Purge of specified Snapshot range complete. If you wish to ROLLBACK
SQL> prompt the purge, it is still possible to do so. Exitting from SQL*Plus will
the purge, it is still possible to do so. Exitting from SQL*Plus will
SQL> prompt automatically commit the purge.
automatically commit the purge.
SQL> prompt
SQL>
SQL> --
SQL> --
SQL>
SQL> spool off
SQL> set feedback on termout on
SQL> whenever sqlerror continue
SQL> SELECT COUNT(*) FROM STATS$SGASTAT;
COUNT(*)
----------
0
已選擇 1 行。
SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;
COUNT(*)
----------
55
已選擇 1 行。
Metalink的bug描述：Bug No. 4046866中進行了說明，Oracle的sppurge.sql指令碼中刪除STATS$UNDOSTAT表的SQL出現了錯誤，裡面的大於號和小於號寫反了。
而刪除STATS$SNAPSHOT時沒有刪除STATS$UNDOSTAT，可能是Oracle漏掉了這個表。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-374620/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- STATSPACK資料清除（二）
- STATSPACK資料清除（一）
- STATSPACK資料清除（三）
- Statspack之八-刪除歷史資料
- 自動刪除過期的statspack統計資料
- [20170213]刪除資料沒有備份可以恢復嗎.txt
- 刪除相應資料夾的時候提示 有程式正在用
- 新增cookie、刪除cookie、清除cookieCookie
- kettle 實時同步資料（插入/更新/刪除資料）
- win10刪除檔案沒有提示怎麼辦 win10刪除檔案沒有提示的方法Win10
- 刪除資料
- dbca刪除資料庫時選項灰色資料庫
- 生成批量刪除pd使用者多表資料(多表關聯)_dbms_stats
- whk我【資料刪除】你個【資料刪除】的
- win10刪除沒有確定鍵怎麼辦_win10刪除檔案沒有提示該怎麼辦Win10
- indexedDB 刪除資料Index
- Solr刪除資料Solr
- 刪除elasticsearch資料Elasticsearch
- 臨時表空間資料刪除問題
- Laravel 中利用『模型事件』來實現刪除資料時的連帶刪除Laravel模型事件
- 刪除某個檔案或資料夾時，系統提示無法刪除!
- Drop table時候會徹底刪除index, Truncate 時候會清除index 但是index資料還是保留在HIndex
- MongoDB 資料庫建立刪除、表（集合）建立刪除、資料增刪改查MongoDB資料庫
- 刪除資料夾時顯示在另一程式開啟怎麼刪除
- c# 刪除某個資料夾下所有空目錄，解決因刪除空目錄後上一組成空目錄沒有刪除的情況C#
- 資料夾刪除不掉怎麼辦？資料夾無法刪除
- 快速刪除有外來鍵關聯的資料庫的資料資料庫
- 利用ipcrm清除資料庫crash後沒有釋放的記憶體段資料庫記憶體
- MySQL刪除資料表MySql
- indexedDB 刪除資料庫Index資料庫
- 刪除重複資料
- MongoDB之資料刪除MongoDB
- 刪除大表資料
- git刪除資料夾Git
- 批次刪除資料方法
- 刪除重復資料
- 【RAC】刪除RAC資料庫節點（一）——刪除資料庫例項資料庫
- 造資料難,刪除資料容易