Join select on dba_data_filles, dba_free_space, dba_extents etc.

From: Wanni Chua <wanni_at_mbox2.singnet.com.sg>
Date: Thu, 11 Mar 1999 22:47:20 +0800
Message-ID: <MPG.1152584e1c86baa398968b@news.singnet.com.sg>

Hi,

I am trying to write a SQL to select a lists of Total space size, Used space size and Free space size for all the tablespaces.

An example of the select statement :
select substr(a.tablespace_name,0,15)Tablespace,

round(sum(a.blocks)/256)Total_space_MB,
round(sum(b.blocks)/256)Used_space_MB,
round(sum(c.blocks)/256)Free_space_MB

from dba_extents a, dba_extents b, dba_free_space c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name group by a.tablespace_name

The problem with this join query is:
1. It takes a very very long time to run the query/ 2. The results returned did not seems to tally when compared to running these queries individually. Eg, Total_space_MB seems to be bigger...etc

Anyone has encounterd a smiliar experience? Why is this happening. How should I write such select query then?

Thanks for your replies Received on Thu Mar 11 1999 - 08:47:20 CST