Re: Help with query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 7 Dec 2006 19:47:26 +0100
Message-ID: <457861bd$0$23135$426a74cc@news.free.fr>

"Chuck" <skilover_nospam_at_bluebottle.com> a écrit dans le message de news: nRXdh.186$7h.26_at_trnddc02...
| -----BEGIN PGP SIGNED MESSAGE-----
| Hash: SHA1
|
| I'm trying to come up with a query of v$undostat that will show the
| total # of undo blocks used during any two hour period. The output
| should look like this..
|
| begin_time end_time undoblks
| ========== =========== ===========
| 09:00 11:00 123
| 09:10 11:10 145
| 09:20 11:20 1032
|
| You get the picture. It's essentially the same thing that "Select
| begin_time, end_time, undoblks from v$undostat" would show if each row
| was 2 hours apart instead of 10 minutes apart.
|
| TIA
| -----BEGIN PGP SIGNATURE-----
| Version: GnuPG v1.4.5 (MingW32)
|
| iEYEARECAAYFAkV4SdMACgkQzIf+rZpn0oRP5wCfbQgQ0RDy6oBm5exvkYUsLCNm
| jZEAn3I/T2ctqgwj1OrtHD8DblZwUAoa
| =4KyX
| -----END PGP SIGNATURE-----
Here's another query which works even if the interval between 2 rows is not 10 minutes:

select begin_time,

       last_value(end_time)
         over (order by begin_time
               range between current row
                         and interval '1:59:59' hour to second following)
         end_time,
       sum(undoblks)
         over (order by begin_time
               range between current row
                         and interval '1:59:59' hour to second following)
         undoblks

from v$undostat
order by begin_time
/

Regards
Michel Cadot Received on Thu Dec 07 2006 - 12:47:26 CST