Oracle-L: RE: SQL Query

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 14 Nov 2003 11:39:25 -0800
Message-ID: <F001.005D6B13.20031114113925@fatcity.com>

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: RROGERS_at_galottery.org

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: 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 Fri Nov 14 2003 - 13:39:25 CST