Re: sql for utilization rpt?

"Doug O'Leary" <dkoleary_at_cb23513-b.rmvll1.il.home.com> wrote in message news:fcF57.490$Icm.147718402_at_news.randori.com...
>
> Hi;
>
> I'm trying to figure out how to get utilization information
> from an Oracle database based on tablespace, not on datafile.
> The report I'm evisioning would look something like:
>
> Tablespace Allocated Used Free %_Util
>
> I realize that the report doesn't take into account tablespace
> fragmentation; however, it should provide good information on
> utilization.
>
> I just can't figure out the sql and it's beginning to bug me -
> which is particularly embarrasing because I'm hoping to take
> the sql test sometime soon.
>
> I can get allocated and free; Used is obviously the
> difference bewteen the and % used is Used/alloc * 100 - how do
> I combine that into one sql query?
>
> I've tried various group bys, correlated subqueries, nothing works
> so far. It either gives syntax errors or bogus data.
>
> Any suggestions or is this something for PL/SQL? Thanks for your
> time
>
> Doug
>
> --
> ------------------------
> Douglas K. O'Leary
> Senior System Administrator
> dkoleary_at_mediaone.net

Use inline views like this

select t.tablespace_name, df.dfbytes
from
dba_tablespaces t
, (select tablespace_name, sum(bytes) dfbytes

    from dba_data_files
    group by tablespace_name) df
where df.tablespace_name = t.tablespace_name

dba_segments and dba_free_space should be dealt with in a similar fashion. I leave this as an exercise for you.
Inline views are part of the exam, aren't they?

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Thu Jul 19 2001 - 13:41:49 CDT