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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
