Re: Help with query

Chuck wrote:
> -----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-----
I just threw this together off the top of my head so it may well have more than a few problems.

col begin_time format a10
col end_time format a10

SELECT TO_CHAR(begin_time, 'HH24:MI') BEGIN_TIME, LEAD(TO_CHAR(end_time, 'HH24:MI'), 11, 0) OVER (ORDER BY begin_time) END_TIME,
undoblks
FROM gv$undostat;

but hopefully will point you in one direction.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org