I encountered the same issues. Also, just to make things interesting, if
you replace DBA_EXTENTS with DBA_FREE_SPACE, the number of MEGS_ALLOCATED is
different. Still wrong, mind you, but different.
None of this makes any sense to me. My bet is that I got me a bug.
Bambi.
-----Original Message-----
Sent: Friday, November 14, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L
Bambi,
I tried your sql on my test server and the used space is the same.
here are the results. The ALLOCATED and PCT are way out, I'm looking.
1 select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100
pct
2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
3 from dba_extents group by tablespace_name) a,
4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
5 from dba_data_files group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name
7* and a.tablespace_name='AWSR_DATA_01'
inux>
inux>/
ABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
----------------------------- -------------- ---------- ----------
WSR_DATA_01 40 38 95
1 select a.tablespace_name,sum(b.bytes)/(1024*1024)
megs_allocated,
2 sum(a.bytes)/(1024*1024) megs_used,
3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct
4 from dba_extents a, dba_data_files b
5 where a.tablespace_name=b.tablespace_name
6 and a.tablespace_name='AWSR_DATA_01'
7* group by a.tablespace_name,b.tablespace_name
linux>/
TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
------------------------------ -------------- ---------- ----------
AWSR_DATA_01 760 38 5
linux>
Bambi,
The problem is a sum of the bytes each tome the tablespace_name is
looked up in the dba_extents table.
With both tables used..
1 select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated
2 from dba_extents a, dba_data_files b
3 where a.tablespace_name=b.tablespace_name
4 and a.tablespace_name='AWSR_DATA_01'
5* group by b.tablespace_name
linux>/
TABLESPACE_NAME MEGS_ALLOCATED
------------------------------ --------------
AWSR_DATA_01 760
With one table used..
1 select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated
2 from dba_data_files b
3 where b.tablespace_name='AWSR_DATA_01'
4* group by b.tablespace_name
linux>/
TABLESPACE_NAME MEGS_ALLOCATED
------------------------------ --------------
AWSR_DATA_01 40
CORRECT ANSWER.
select count(*) from dba_data_files where tablespace_name
='AWSR_DATA_01'
COUNT(*) = 1
select count(*) from dba_extents where tablespace_name ='AWSR_DATA_01'
COUNT(*) = 19
19 X 40 = 760
Ron
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
--
Author: Bellow, Bambi
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
