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

Denny,

You could give him(her) a little more.....

Like :

select Tablespace, sum(Total_space_MB), sum(Used_space_MB), sum(Free_space_MB)

from (
select substr(a.tablespace_name,0,15)Tablespace, round(sum(a.bytes)/1024/1024) Total_space_MB, 0 Used_space_MB,
0 Free_space_MB
from dba_data_files a
group by a.tablespace_name
union
select substr(a.tablespace_name,0,15)Tablespace, 0 Total_space_MB,
round(sum(a.bytes)/1024/1024) Used_space_MB, 0 Free_space_MB
from dba_extents a
group by a.tablespace_name
union
select substr(a.tablespace_name,0,15)Tablespace, 0 Total_space_MB,
0 Used_space_MB,
round(sum(a.bytes)/1024/1024) Free_space_MB from dba_free_space a
group by a.tablespace_name
) combo
group by combo.tablespace
/

Or better yet, a little less

Like :

Try ILV ?!?!??!?

Regards,

Marc Mazerolle

Denny Koovakattu wrote:

> 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 - 12:11:54 CST