STATSPACK清除数据时没有删除STATS$UNDOSTAT

Oracle在清除STATSPACK数据的时候，漏掉了STATS$UNDOSTAT表。

无论是删除STATS$SNAPSHOT表的时候，还是使用SPPURGE.SQL脚本，都无法清除这张表的记录。

SQL> SELECT SNAP_ID FROM STATS$SNAPSHOT;

   SNAP_IDITPUB个人空间i?;GP?.Ua'V
----------ITPUB个人空间6_*Y!H3?5B
         1

SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;

  COUNT(*)
1f(I;K;SaU L0----------
7["@-I.A-x&y0        55

SQL> SELECT COUNT(*) FROM STATS$SGASTAT;

  COUNT(*)ITPUB个人空间1bb1QCd/Pq|S
----------
1Z(N"`LF+? {0        41

SQL> DELETE STATS$SNAPSHOT;

已删除1行。

SQL> SELECT COUNT(*) FROM STATS$SGASTAT;

  COUNT(*)ITPUB个人空间XT1y+g'm:iK#{
----------ITPUB个人空间%q[ rB"@ b0tY$m? a
         0

SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;

  COUNT(*)ITPUB个人空间-T:r^|u1h$w0R
----------
Wvoo5gx!p^mh0        55

使用SPPURGE.SQL脚本也是一样：

SQL> ROLLBACK;

回退已完成。

SQL> @?/rdbms/admin/sppurge.sql
o:Gl.B7m.W ^ w0SQL> RemITPUB个人空间%m5{tre6P;_]@c
SQL> Rem $Header: sppurge.sql 20-mar-2002.18:02:43 vbarrier Exp $ITPUB个人空间*KLgu
hqA/C
SQL> RemITPUB个人空间Q8FP+Q s
o6Ir
SQL> Rem sppurge.sqlITPUB个人空间B)^e&@ DQ
SQL> RemITPUB个人空间rr6P?j~
SQL> Rem Copyright (c) 2000, 2002, Oracle Corporation.  All rights reserved.ITPUB个人空间N1|2n0R w
SQL> Rem
K|lAf;q)Wo0SQL> Rem    NAME
+`1oj/_6gFoV#D;g0SQL> Rem         sppurge.sql - STATSPACK Purge
4Z
@(E&q)Od'Ed)E0SQL> RemITPUB个人空间D
{ O"it8UG A
SQL> Rem    DESCRIPTION
D4W|9B`!S0@0SQL> Rem         Purge a range of Snapshot Id's between the specifiedITPUB个人空间4T t4I @
G
n9y
SQL> Rem         begin and end Snap Id's
9xVI5\Q0x
D,o3[0SQL> RemITPUB个人空间-[4^.c8q(L W K:p"|
SQL> Rem    NOTES
:{6LuF d pwuE0SQL> Rem         Should be run as STATSPACK user, PERFSTAT.ITPUB个人空间|1O KUTo_
SQL> RemITPUB个人空间*t Z@bTO bu,VP
SQL> Rem         Running purge may require the use of a large rollbackITPUB个人空间1E.g:Y@S
SQL> Rem         segment; to avoid rollback segment related errorsITPUB个人空间7h1J
cGf*T
SQL> Rem         explicitly specify a large rollback segment before runningITPUB个人空间zR Vm Ers8r
SQL> Rem         this script. by using the 'set transaction use rollback segment..'ITPUB个人空间 i ]$m1hM+H
SQL> Rem         command, or alternatively specify a smaller range of
,M
ZZIW0SQL> Rem         Snapshot Id's to purge.ITPUB个人空间g:@#C
Q T
SQL> RemITPUB个人空间rn-g4b%X&jTr }
SQL> Rem
X*Mg~'I[ F6M"H0SQL> Rem    MODIFIED   (MM/DD/YY)ITPUB个人空间|? lUkK
SQL> Rem    vbarrier    03/20/02 - Optional stats$seg_stat_obj purge
1Z0r ]G0pvY}%q%rm3cuN0SQL> Rem    cdialeri    04/12/01 - 9.0
4b:q4m9E.dI0T"~0SQL> Rem    cdialeri    04/11/00 - 1261813ITPUB个人空间X| ny7R9S
SQL> Rem    cdialeri    03/15/00 - Conform. to new structureITPUB个人空间(\7cNTh"L9M?X
SQL> Rem    densor.uk   05/00/94 - Allow purge of range of snaps
*M|'f)f#K$V}0SQL> Rem    gwood.uk    10/12/92 - Use RI for deletes to most tables
3_y:PC9e
A0SQL> Rem    cellis.uk   11/15/89 - Created
Iz#WX(C*c&Q!@ G0SQL> Rem
+i-x)|?"g{%i'LN0Z0SQL>ITPUB个人空间d_sR*T6G
SQL> set feedback off verify off pages 999
M-XT4tEg0SQL> undefine dbid inst_num losnapid hisnapid
e!Rbl6`6q0SQL> whenever sqlerror exit rollback
+sCA3bm?h3f!`Z0SQL>ITPUB个人空间qQ0U#Ye@r
SQL> spool sppurge.lis
y4t'[Rb5c{;ri*nM0SQL>
n%r8X v'TW0SQL>
u~.T-v]0SQL> /* ------------------------------------------------------------------------- */ITPUB个人空间I]8TA*f
p
SQL>ITPUB个人空间q&v.Z9w9I
SQL> --
aY6V[{WL0SQL> -- Get the current database/instance information - this will be used
LH+G1K3dc}l#N0SQL> -- later in the report along with bid, eid to lookup snapshotsITPUB个人空间ydoZQ:n7M'K
SQL>ITPUB个人空间2|3W s0HFrG
SQL> prompt

SQL> prompt

SQL> prompt Database Instance currently connected toITPUB个人空间\7B5o9yv+{YO i\7|
Database Instance currently connected to
}/|k iA`0SQL> prompt ========================================
5E|L8g ~ }5t|0========================================ITPUB个人空间0C3GwV2Ya
SQL>ITPUB个人空间,zK:r!^7G2bF\
SQL> column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
,R4s)N8nH;F0SQL> column inst_name heading "Instance|Name"  new_value inst_name format a10;
d)^F.r T,`5T'L0SQL> column db_name      heading "DB Name"   new_value db_name   format a10;ITPUB个人空间_?jC ?pC@
SQL> column dbid         heading "DB Id"          new_value dbid      format 9999999999 just c;
!L0i;j5g(x,f#Dd!M7lZ0SQL> select d.dbid            dbid
Pr8e3\|1C Ve%}2Qm0  2       , d.name            db_name
0qJizt _ z7E1WQo:~0  3       , i.instance_number inst_num
5gh8PV.L;Z3?[0  4       , i.instance_name   inst_nameITPUB个人空间,Fp[^7UR+Y6c
  5    from v$database d,ITPUB个人空间:w[\;y2\
  6         v$instance i;

                                InstanceITPUB个人空间#j'WPG.s;H p
   DB Id    DB Name    Inst Num NameITPUB个人空间3h#f L3IH9Pg3e
----------- ---------- -------- ----------ITPUB个人空间1Vk[m']#D(`X4[/pX
 2270762593 TESTDATA          1 testdataITPUB个人空间&r)xP;N,PrwyV7\
SQL>ITPUB个人空间 MI'LN1wp
HT
SQL> variable dbid       number;
a(ch'UD2R0SQL> variable inst_num   number;
&A2H%E&Ga6s!lyLh0w6dI0SQL> variable inst_name  varchar2(20);ITPUB个人空间pT1VOV
SQL> variable db_name    varchar2(20);
1tD_)p
Bo4D0P0SQL> beginITPUB个人空间'D|*hKr\
  2    :dbid      :=  &dbid;
9E)` \J$[Vlr0  3    :inst_num  :=  &inst_num;
#?-Q!zX!U2Xx0  4    :inst_name := '&inst_name';ITPUB个人空间1b{sz5y't
  5    :db_name   := '&db_name';
3R.L~6O#r(Qvp:g.X'u9L0  6  end;ITPUB个人空间;CqAC?wjJFQ
  7  /ITPUB个人空间a0NC:|f9g/D]
SQL>ITPUB个人空间X r&su"m-V?5N
SQL>ITPUB个人空间I3xd;SS
SQL> --
6GCG&J.@0SQL> --  List Snapshots
'Wjo-IQ4V+rZ|8t0SQL>
U0g&lxN x0SQL> column snap_id       format 9999990 heading 'Snap Id'
`@$m![c&G"Y*D0SQL> column snap_date     format a21   heading 'Snapshot Started'ITPUB个人空间:CaV/lyX z
SQL> column host_name     format a15   heading 'Host'
"t k7s{,FB1M0SQL> column parallel      format a3    heading 'OPS' trunc
? z&W LZ:@F0SQL> column level         format 99    heading 'Snap|Level'
!y-q7fNY,o0SQL> column versn         format a7    heading 'Release'
Q@*s mHqT0SQL> column ucomment          heading 'Comment' format a25;
p5a)O(WP_0SQL>
NfZ D&oZ/a0SQL> prompt

SQL> prompt

SQL> prompt Snapshots for this database instance
(v
TD:j3t1vCu0Snapshots for this database instance
fH)z"ZST0SQL> prompt ====================================

E$@"u|7`XlfD0====================================ITPUB个人空间 @W$P t8rSL
SQL>ITPUB个人空间7E2nC]1](~%IU#L
SQL> select s.snap_id
&qG \lo(O5pV2D0  2       , s.snap_level                                      "level"ITPUB个人空间%ca8J&D.XmR'p,a
  3       , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')    snap_dateITPUB个人空间`C(j?%x!Q#i1u)@^Hb
  4       , di.host_name                                      host_name
(y?WPb.r7M0  5       , s.ucommentITPUB个人空间2i xIC3`M:mU
  6    from stats$snapshot sITPUB个人空间 @%k2]7W!\
  7       , stats$database_instance diITPUB个人空间C^-@ n5B-}+^7^p
  8   where s.dbid              = :dbid
6Cu&LL tZT0  9     and di.dbid             = :dbid
ZW6j*o?O|0 10     and s.instance_number   = :inst_numITPUB个人空间:R.An.y0?or6d%f
 11     and di.instance_number  = :inst_numITPUB个人空间 U9g Ux {/\?
 12     and di.startup_time     = s.startup_timeITPUB个人空间+Q7hf4^y
 13   order by db_name, instance_name, snap_id;

          SnapITPUB个人空间M)P(e E8O
 Snap Id Level Snapshot Started      Host            Comment
T/q"M E!t1S?0-------- ----- --------------------- --------------- -------------------------
%{w?\1^-[(i.I0       1     5  07 7月  2008 16:35:4 localhost.localITPUB个人空间)fNX&Lw'R/U\%Hc4dR
               1                     domain

SQL>ITPUB个人空间,E+B2U;UKPGE
SQL>
bpA+u?(f1X#mf%~0SQL>ITPUB个人空间7yAd0e/U:g9dH"Zjd
SQL> --
tP]7M4^9C6es/Lc0SQL> --  Post warning

ud7AnT%e5@0SQL>
:E#_;kAunW-XAT y!f0SQL> prompt

SQL> prompt

SQL> prompt Warning
Mj/CI.Xf||-@0Warning
-tcqk#c6b5@8g7q `%o0SQL> prompt ~~~~~~~ITPUB个人空间}SlU f
~UHvG
~~~~~~~ITPUB个人空间!f-K~#eYW7E3m
SQL> prompt sppurge.sql deletes all snapshots ranging between the lower and
zGH'P5^ vP-x0sppurge.sql deletes all snapshots ranging between the lower andITPUB个人空间7tuigG
SQL> prompt upper bound Snapshot Id's specified, for the database instance
G0].U(A+]r4yX0upper bound Snapshot Id's specified, for the database instance
)|N8Xt|0[0s(\E0SQL> prompt you are connected to.
pFV"c[)])b3`0you are connected to.
oM3vL^vf0SQL> prompt

SQL> prompt You may wish to export this data before continuing.
pA'U Y#K s0You may wish to export this data before continuing.ITPUB个人空间1l1F
T1u(\L)_
SQL> prompt

SQL>
Q;JQU%M(NE7a0SQL>ITPUB个人空间'|'Ju+C0G {"C{
SQL>

gUlO({
~7U_
_~0SQL> --ITPUB个人空间Q"~;n xT"s
SQL> --  Obtain snapshot rangesITPUB个人空间y0Tb;Ld[ t|!k
SQL>
`c;W"[0@fS2q)d'`j0SQL> prompt

SQL> prompt Specify the Lo Snap Id and Hi Snap Id range to purgeITPUB个人空间A `Ah:~-V
Specify the Lo Snap Id and Hi Snap Id range to purgeITPUB个人空间9B3Lr@S`
SQL> prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ITPUB个人空间Mz!`$YH)}gn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ITPUB个人空间:tf8`%lvSV
SQL> prompt Using &&LoSnapId for lower bound.ITPUB个人空间3gIC:_c!x
输入losnapid的值:  1ITPUB个人空间,{ V]%hfS7j
Using 1 for lower bound.
PISE@3z8|0SQL> prompt

SQL> prompt Using &&HiSnapId for upper bound.
6M2Z[-w Z"T @0输入hisnapid的值:  1
1epHG.a/{']t0Using 1 for upper bound.ITPUB个人空间-m4ij"iOa+B
SQL>ITPUB个人空间a6R1FuJq
SQL>
Rk)op6Q!dP0SQL> variable lo_snap   number;ITPUB个人空间|J7rF^
SQL> variable hi_snap   number;ITPUB个人空间4y#?'Ju
z;|s(z;_
q
SQL> beginITPUB个人空间{we@"G
  2    :lo_snap   :=  &losnapid;
#i!`a7u'DY4{0  3    :hi_snap   :=  &hisnapid;
\^q `H%S0  4  end;ITPUB个人空间HE!}8F?:C8Q
  5  /ITPUB个人空间2\"\/N?/f0e
SQL>
7CF/w{6G4s0SQL> set termout off
MkS@`^b0SQL>
'V?"K\*gE0SQL> set heading off
L {y0_C6m_!E.F0C0SQL>
u9lf8x YG9]0SQL> select 'WARNING: LoSnapId or HiSnapId specified does not exist in STATS$SNAPSHOT'ITPUB个人空间8Wvn+GA] H8s|
  2    from dualITPUB个人空间tX
szSv%I
  3   where not exists
Pi0hwR2J3_0  4        (select null
:h*t;L g,s| w
E/P0  5           from stats$snapshotITPUB个人空间'YF/~A!_]
  6          where instance_number = :inst_num
9a$mh)a3gA]8k0  7            and dbid            = :dbidITPUB个人空间~oGz
`!u7G6hD&fa
  8            and snap_id         = :lo_snap)
Xv$j9Qi&T|&~4s8g,j)|0  9      or not existsITPUB个人空间kl%k]n$Gha$mVFJR
 10        (select nullITPUB个人空间h\},G8Gl
 11           from stats$snapshot
Ku?+u/VSt.]0 12          where instance_number = :inst_numITPUB个人空间Wrf}[(Z,F^!q#t
O
 13            and dbid            = :dbidITPUB个人空间 f z
H(ON `b
 14            and snap_id         = :hi_snap);
q1|`.BfU+D:_r;}Y(}0SQL>
R8a\ q cg0SQL> set heading onITPUB个人空间3BS{b
}
I9]Z#n#{ J
SQL>ITPUB个人空间5yVa$eL }2oV
SQL>ITPUB个人空间
D.U2q _fr!^
SQL>ITPUB个人空间J(Tmnj
SQL> --ITPUB个人空间b[
]*VK7]5i
SQL> --  Delete all data for the specified ranges
)uJyU'_6Mk(?KjH0SQL>ITPUB个人空间$ZL6d3w+D;\
SQL> /*  Use RI to delete parent snapshot and all child records  */ITPUB个人空间;z#g/xns:m
SQL>ITPUB个人空间:Q
tY(O?^8|,@"f
SQL> prompt

SQL> prompt Deleting snapshots &&losnapid - &&hisnapid..ITPUB个人空间 ?m&d3EX1Q;x
Deleting snapshots 1 - 1.ITPUB个人空间2l8O)O8kc7_|
SQL> delete from stats$snapshot
g Z%Y&K$hq!_l0  2   where instance_number = :inst_num
R$gN9^l0  3     and dbid            = :dbidITPUB个人空间LJL#FcCdk
  4     and snap_id between :lo_snap and :hi_snap;ITPUB个人空间p9ei6{8GW)`s
SQL>ITPUB个人空间_Mq)|)Bi
SQL>
&]
WHM;Q.a7zvI0SQL> set termout off;ITPUB个人空间6v6]9Q]n;~C
SQL>ITPUB个人空间2V
e3~RI8s
SQL>ITPUB个人空间lE;B`afN9f$s
SQL>ITPUB个人空间0V e,NK!os)hb
SQL> /*  Delete any undostat rows that cover the snap times     */ITPUB个人空间;lsC
fG
SQL>ITPUB个人空间5_^\:Bdd e"])[
SQL> delete from stats$undostat us
`5tE4?Yk8|I0  2   where dbid            = :dbidITPUB个人空间.y0jO)ur4b)@&D
  3     and instance_number = :inst_numITPUB个人空间s'xYK%a&e:B
  4     and begin_time      <  to_date(:btime, 'YYYYMMDD HH24:MI:SS')
J$Lq&uG a0  5     and end_time        >  to_date(:etime, 'YYYYMMDD HH24:MI:SS');
!ye[/e
oc0SQL>
9H/y,T'F4cB#K}0SQL>ITPUB个人空间9X.v az?8V
SQL>
Zn!?,E#_$x6j w%|0SQL> /*  Delete any dangling database instance rows for that startup time  */
3e6{O%Xn @5lGr0SQL>
sq%Nl Ae0SQL> delete from stats$database_instance diITPUB个人空间s]V?T]
Y)Z&KDz
  2   where instance_number = :inst_num
&~@ IzP'B~$PS7v1h0  3     and dbid            = :dbidITPUB个人空间9Fo^9jZL.n le
  4     and not exists (select 1ITPUB个人空间,gY,y |"YH*mwdP
  5                       from stats$snapshot s
1m@0?'_%B%`k&yYh0  6                      where s.dbid            = di.dbidITPUB个人空间
H:r*q|"z^O`2x,dy.D
  7                        and s.instance_number = di.instance_numberITPUB个人空间*M \a{g!t`^_F!?^
  8                        and s.startup_time    = di.startup_time);
'pEB2V7s Wqn }1ti0SQL>
X,PvyVfu y0SQL>ITPUB个人空间o-@-^
VTFY
SQL>
/wL5@8W8{?XrT'p0SQL> /*  Delete any dangling statspack parameter rows for the database instance  */ITPUB个人空间Lo(R y@7}T
SQL>
QFR;k0N!S
X E2x0SQL> delete from stats$statspack_parameter sp
J|Az3QB0  2   where instance_number = :inst_numITPUB个人空间A Hz5bW9w
  3     and dbid            = :dbidITPUB个人空间utt;~k
  4     and not exists (select 1
O/P)\P9\Wt0  5                       from stats$snapshot s
@-cg&a$T(IK/e0  6                      where s.dbid            = sp.dbid
~1G7E BC1{x{+Ak!d0  7                        and s.instance_number = sp.instance_number);ITPUB个人空间v/Pz.snbE
SQL>
y5s1z5\*n2NKo y0SQL>ITPUB个人空间|iKj&o
SQL> --ITPUB个人空间k3zLVd.Z)R:V^
B
SQL> --
u(Q
h6B:eL(e^7Y0SQL>
,t Hx&E;q w-o0SQL> prompt

SQL> prompt

SQL> prompt Purge of specified Snapshot range complete.  If you wish to ROLLBACKITPUB个人空间w3UZ N"y z%o5W
Purge of specified Snapshot range complete.  If you wish to ROLLBACKITPUB个人空间!~;s_1h1Oq
SQL> prompt the purge, it is still possible to do so.  Exitting from SQL*Plus willITPUB个人空间X,@? _ B.p
the purge, it is still possible to do so.  Exitting from SQL*Plus willITPUB个人空间$B2^&T,~W
SQL> prompt automatically commit the purge.ITPUB个人空间4s.v/F8{!{.U
automatically commit the purge.
_] T2wY],QCi wzU0SQL> prompt

SQL>ITPUB个人空间S ]-P k5yn
SQL> --ITPUB个人空间'q6A/we?}
SQL> --ITPUB个人空间2Rk%E\EF7W
SQL>
)Uw"gib$b0SQL> spool offITPUB个人空间J,_V)p
@ke
SQL> set feedback on termout on
Hq%c`p_2R0SQL> whenever sqlerror continueITPUB个人空间!]
b3tqr
SQL> SELECT COUNT(*) FROM STATS$SGASTAT;

  COUNT(*)
5ns!?!hK1V[0----------ITPUB个人空间&arD~x
         0

已选择1行。

SQL> SELECT COUNT(*) FROM STATS$UNDOSTAT;

  COUNT(*)
D+r)],sb/Y0----------ITPUB个人空间8V z-y/hED
        55

已选择1行。

Metalink的bug描述：Bug No. 4046866中进行了说明，Oracle的sppurge.sql脚本中删除STATS$UNDOSTAT表的SQL出现了错误，里面的大于号和小于号写反了。

而删除STATS$SNAPSHOT时没有删除STATS$UNDOSTAT，可能是Oracle漏掉了这个表。