RE: sql difference rounds

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Wed, 26 Jun 2002 12:34:38 -0800
Message-ID: <F001.004894CD.20020626123438@fatcity.com>

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

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kkennedy
  INET: kkennedy_at_firstpoint.com

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).

Received on Wed Jun 26 2002 - 15:34:38 CDT