Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: VAR : does anybody have a clue?
A copy of this was sent to kal121_at_yahoo.com
(if that email address didn't require changing)
On Fri, 12 Nov 1999 21:50:17 GMT, you wrote:
>This is what I am trying to do:
>
>SQL> var x number -- declare a variable named x
>
>Now, what I want to do is assign:
>select sum(bytes) from v$sgastat INTO x
>
>to be used as follows:
>
>SELECT name, bytes, bytes/x FROM v$sgastat;
>
>bytes/x would give me the percent of the sga devoted to that particular
>sga component in the third column.
>
>Does anybody know the CORRECT way to do this?
>
it would be:
tkyte_at_8.0> var x number
tkyte_at_8.0> exec select sum(bytes) into :x from v$sgastat
PL/SQL procedure successfully completed.
tkyte_at_8.0> select name, bytes, bytes/:x from v$sgastat 2 /
NAME BYTES BYTES/:X ------------------------------ ---------- ---------- fixed_sga 44924 .000400538 db_block_buffers 81920000 .730391468 [snip] fixed allocation callback 928 8.2740E-06 type object de 22740 .000202748
37 rows selected.
Although it might be better to code:
tkyte_at_8.0> select name, bytes, bytes/sum_bytes 2 from v$sgastat, ( select sum(bytes) sum_bytes from v$sgastat ) 3 /
NAME BYTES BYTES/SUM_BYTES ------------------------------ ---------- --------------- fixed_sga 44924 .000400538 db_block_buffers 81920000 .730391468 [snip] fixed allocation callback 928 8.2740E-06 type object de 22740 .000202748
37 rows selected.
as that works in all environments (doesn't require sqlplus'isms)
>Thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Nov 13 1999 - 09:08:46 CST