Re: Free space by tablespace script, quality control check.

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/27
Message-ID: <8jatnv$p16$1@nnrp1.deja.com>#1/1

In article <8jaqqn$mhe$1_at_nnrp1.deja.com>,   Ethan Post <epost1_at_my-deja.com> wrote:
> This script reports current tablespace size, total space already
> allocated that it can grow in to, and total size the datafiles it's on
> can grow to, then total percentage used. I'm going to use some of
 these
> stats in a tool I'm building please let me know if you see any obvious
> problems with the logic.
>
> Thanks,
> Ethan
> http://www.freetechnicaltraining.com
> http://www.gnumetrics.com
> *************************
>

well, its a little slow -- but an interesting idea. I've had a script for a while that shows

Tablespace Name
KBytes allocated
KBytes Used
KBytes Free
percentage Used
Largest free contigous extent

I added to it easily

max possible size the tablespace could grow to the amount of that max possible size that is allocated.

The script is:

---

• free.sql --
• This SQL Plus script lists freespace by tablespace

  ---

column dummy noprint

column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"

column Kbytes format 999,999,999 heading "KBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" column max_size format 999,999,999 heading "MaxPoss|Kbytes" column pct_max_used format 999.9 heading "%|Max|Used" break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,

             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest,
       nvl(kbytes_max,kbytes_alloc) Max_Size,
       (kbytes_alloc/kbytes_max)*100 pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name order by &1
/

and it goes pretty fast. Instead of using dba_segments to add up how much space is allocated (slow) I use dba_free_space to see how much is FREE and subtract that from the total space (faster).

> select
> tablespace_name,
> round(sum(current_mb)) current_mb,
> round(sum(total_mb)) total_mb,
> round(sum(max_mb)) max_mb,
> trunc(decode(sum(max_mb), 0, sum(current_mb)/sum(total_mb), sum
> (current_mb)/sum(max_mb))*100) percentage
> from
> (
> select
> tablespace_name,
> sum(bytes)/1024/1024 current_mb,
> 0 total_mb,
> 0 max_mb
> from
> dba_segments
> group by
> tablespace_name
> union
> select
> tablespace_name,
> 0 current_mb,
> sum(bytes)/1024/1024 total_mb,
> sum(maxbytes)/1024/1024 max_mb
> from
> dba_data_files
> group by
> tablespace_name)
> group by
> tablespace_name
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Jun 27 2000 - 00:00:00 CDT