Re: Join select on dba_data_filles, dba_free_space, dba_extents etc.

Try inline views.

Denny

In article <MPG.1152584e1c86baa398968b_at_news.singnet.com.sg>,   wanni_at_mbox2.singnet.com.sg (Wanni Chua) wrote:
> 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 11 1999 - 11:48:20 CST