Re: sql question

From: terry norman <scott_at_tiger9.freeserve.co.uk>
Date: 1999/11/11
Message-ID: <80d1uq$cag$1_at_news7.svr.pol.co.uk>#1/1


Guys,

The code I use looks a lot like this, i.e the two subqueries, but I found I needed an outer join,

where a.tablespace_name = b.tablespace_name(+)

otherwise tablespaces with no free space rows in dba_free_space (i.e. completely full ones) don't get reported correctly.

Regards,
Terry.

Babu <vsbabu_at_erols.com> wrote in message news:80cfud$la2$1_at_autumn.news.rcn.net...
> Here is the one I use. Check my website at
> http://www.csoft.net/~vsbabu/php.php3
> for an online tool called OraHoo! which uses this and many other scripts
 to
> provide
> online reporting via the web using PHP.
>
> select a.TABLESPACE_NAME,
> a.BYTES bytes_used,
> b.BYTES bytes_free,
> b.largest,
> round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
> from
> (
> select TABLESPACE_NAME,
> sum(BYTES) BYTES
> from dba_data_files
> group by TABLESPACE_NAME
> )
> a,
> (
> select TABLESPACE_NAME,
> sum(BYTES) BYTES ,
> max(BYTES) largest
> from dba_free_space
> group by TABLESPACE_NAME
> )
> b
> where a.TABLESPACE_NAME=b.TABLESPACE_NAME
> order by ((a.BYTES-b.BYTES)/a.BYTES) desc
>
>
> --
> V.Satheesh Babu http://csoft.net/~vsbabu
> vsbabu_at_csoft.net
> vsbabu_at_erols.com
>
>
Received on Thu Nov 11 1999 - 00:00:00 CET

Original text of this message