RE: sql difference rounds

Kevin,

Thank you, this works well. I like it better on one line, too!

I wonder what about my system that the original came out rounded? I'm on the same version of Oracle but 64-bit and Solaris 8 64-bit.

Debi

At 12:34 PM 6/26/2002 -0800, kkennedy wrote:
>Hi Debi,
>I ran your script on my system and did not have the rounding problem
>(sqlplus 8.1.7.0.0 running on W2K against an 8.1.7.3.0 database on Solaris).
>
>Maybe you can try this query instead:
>
>select t.tablespace_name
>, t.total_bytes
>, u.used_bytes
>, t.total_bytes-u.used_bytes space_left
>from (select tablespace_name, sum(bytes) total_bytes
> from dba_data_files
> group by tablespace_name) t
> , (select tablespace_name, sum(bytes) used_bytes
> from dba_extents
> group by tablespace_name) u
>where t.tablespace_name=u.tablespace_name
>and t.tablespace_name=&1
>/
>
>HTH
>Kevin Kennedy
>First Point Energy Corporation
>-----Original Message-----
>Sent: Wednesday, June 26, 2002 12:20 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Why is my difference rounded:
>
>'Checking space left in data_ts:'
>
> TOTAL_BYTES
>
>--------------------
>
> 18,880,659,456
>
>
> USED_BYTES
>
>--------------------
>
> 17,796,554,752
>
> Space
>Left
>--------------------
>
> 1,000,000,000
>
>
>Here's my script:
>
>set heading on verify off
>col total_bytes format 999,999,999,999,999 new_value tbytes
>col tbytes format 999,999,999,999,999
>col used_bytes format 999,999,999,999,999 new_value ubytes
>col ubytes format 999,999,999,999,999
>col space_left format 999,999,999,999,999 head 'Space Left'
>
>spool space_left
>prompt
>prompt 'Checking space left in &1:'
>prompt
>select sum(t.bytes) total_bytes
>from dba_data_files t
>where t.tablespace_name = upper('&&1')
>/
>select sum(u.bytes) used_bytes
>from dba_extents u
>where u.tablespace_name = upper('&&1')
>/
>select (&&tbytes) - (&&ubytes) space_left
>from dual
>/
>
>Thanks,
>
>Debi
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Debi
> INET: dlorraine_at_ucdavis.edu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Debi
  INET: dlorraine_at_ucdavis.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).