Re: Help with query

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Thu, 07 Dec 2006 17:55:08 GMT
Message-ID: <0AYdh.190$7h.159@trnddc02>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Chuck wrote:
> 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
Never mind. Figured it out.

SELECT a.begin_time,

         SUM (b.undoblks)*8/ 1024 undo_mb     FROM v$undostat a JOIN v$undostat b

         ON b.begin_time >= a.begin_time
       AND b.begin_time < a.begin_time + 2 / 24

GROUP BY a.begin_time
ORDER BY 1 desc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)

iEYEARECAAYFAkV4VXwACgkQzIf+rZpn0oSeeQCbBr6dojuZf+FsX7exIJShTT8J FLQAoJ7jwKELAotVA0Y42Azx1skVIlwM
=xPf4
-----END PGP SIGNATURE----- Received on Thu Dec 07 2006 - 11:55:08 CST