Re: Freespace report

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 26 Jul 2002 11:05:41 +1000
Message-ID: <r8109.44008$Hj3.133392@newsfeeds.bigpond.com>

Hi Charles,

Good on you. It's always nice to come up with your own scripts to do stuff like this because I believe it all helps in the learning process and understanding what is going on and how Oracle works.

Note that there are events in OEM that provides similar functionality.

Cheers

Richard
"Charles J. Fisher" <cfisher_at_rhadmin.org> wrote in message news:Pine.LNX.4.44.0207251108010.30545-100000_at_galt.rhadmin.org...
> I've seen several queries floating around to report space used in Oracle
> tablespaces and datafiles, but I decided to write my own recently,
> because:
>
> 1. I don't care when a datafile is (nearly) full when the tablespace is
> not (nearly) full.
> 2. I'm tired of seeing 2gig datafiles being reported when Oracle has
> datafiles on a system that does not support large files.
> 3. When the tablespace *is* full, I'd like to see *all* the associated
> datafiles.
> 4. I've also seen inline views summing dba_extents to be faster than
> queries not using inline views (but I'm too lazy to explain the plan).
>
> So the query below reports all datafiles for tablespaces over 90% full.
> The query does stomp on the datafile name a bit (what sql needs is unix
> "basename"). It's a shame that sqlplus "compute" doesn't work over
> multiple columns. It seems to run pretty fast, though.
>
> Just thought I'd share, and see if anyone has something better.
>
> ----------------------------------------------------------------------

---

> / Charles J. Fisher |"Dig within. There lies the
spring /
> / cfisher_at_rhadmin.org | of good: ever dig, and it will
/
> / http://rhadmin.org | ever flow." -Marcus Aurelius
/
> --------------------------------------------------------------------------
-
>
>
> set lin 132
> set pages 2000
> column ts_mb format 9999
> column df_mb format 9999
> column ts_pct format 99.9
> column df_pct format 99.9
>
> break on tablespace_name skip 1
> compute sum of df_mb on tablespace_name
>
> select
> full_ts.tablespace_name,
> full_ts.ts_mb,
> full_ts.ts_pct,
> full_df.df_mb,
> full_df.df_pct,
> substr(full_df.file_name,instr(full_df.file_name,'/',1,4) + 1,25)
> file_name
> from
> (select
> df.tablespace_name,
> df.bytes / 1024 / 1024 ts_mb,
> round(e.bytes/df.bytes, 4) * 100 ts_pct
> from
> (select tablespace_name, sum(bytes) bytes
> from dba_data_files group by tablespace_name) df,
> (select tablespace_name, sum(bytes) bytes
> from dba_extents group by tablespace_name) e
> where
> df.tablespace_name = e.tablespace_name
> and e.bytes/df.bytes > 0.85) full_ts,
> (select
> df.file_name,
> df.file_id,
> df.bytes / 1024 / 1024 df_mb,
> round(sum(e.bytes) / df.bytes, 4) * 100 df_pct
> from
> (select tablespace_name, file_name, file_id, bytes
> from dba_data_files) df,
> (select file_id, sum(bytes) bytes
> from dba_extents group by file_id) e
> where
> df.file_id = e.file_id
> group by
> df.file_name, df.file_id, df.bytes) full_df,
> (select tablespace_name, file_id from dba_data_files) ts_df
> where
> full_ts.tablespace_name = ts_df.tablespace_name
> AND ts_df.file_id = full_df.file_id
> order by
> full_ts.tablespace_name, full_df.file_name
> /
>
Received on Thu Jul 25 2002 - 20:05:41 CDT