Santa's free space.sql

[ksbrace]

Santa,
I was testing out your GREAT freespace.sql script:

http://www.tek-tips.com/viewthread.cfm?qid=881562

and I had a couple of questions.

Does the 90% check against 90% of what the current size is or does it check against 90% of what the tablespace can autoextend to?

For instance, if I have 1 tablespace with 1 datafile and that datafile is:

current space used=95MB
current space free=5MB
which would make datafile size=100MB and
autoextend=on
autoextend maxsize=200MB

Would the query report this as being over 90% full? If so, what I need is something smart enough to do the calls on current space used vs. autoextend maxsize, so really this tablespace is only 90/200 or 45% full.

And also along with this, is there a way I can calculate if the autoextend next extent is smaller than the total space available on the mount point where the datafile resides. Like, if the above datafile has an autoextend next parameter of 100MB, so:

datafile size=100MB
current space used=95MB
current space free=5MB
autoextend=on
autoextend maxsize=200MB
autoextend next=100MB

And the location of the file is on /u01 and u01 currently has 20MB free, when oracle goes to autoextend the datafile it will fail and would want to know about that before it happens too.

Thanks for any advice/help in advance!
Kelly

http://www.tek-tips.com/viewthread.cfm?qid=881562

Great script Santa!

 

[SantaMufasa]

Kelly,

(First, please pardon the additional width of this thread...I had to expand the width to avoid annoying line wrap in the output, below.)

The "freespace.sql" version that appears on the thread that you cited calculates "percent free of current file allocation".

The version, below, calculates "percent free of Potential Total Bytes" (provided, as you mentioned, that the file system has enough space available to accommodate growth to the file's MAXSIZE):

Section 1 -- Alternate "Freespace.sql" code:

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

Section 2 -- Sample invocation of new, modified code:

@freespace
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off

                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- ----------------------------------------------------
DATA1              4    2,097,152,000      293,601,280       78,249,984    2,018,902,016     96 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF'
DATA2              5    2,097,152,000      241,172,480        6,619,136    2,090,532,864     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF'
RBS                2    2,097,152,000       20,971,520           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM             1    2,097,152,000      205,520,896      196,878,336    1,900,273,664     90 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3    2,097,152,000       20,971,520            8,192    2,097,143,808     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      782,237,696      281,821,184   10,203,938,816


Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
*********************************************************************************************************************************************************
SQL>

And yes, we certainly could write code to warn if a file system lacks the necessary free bytes to accommodate a file's NEXT extension. I just haven't done that yet. If you can wait until I have some "recreational coding time" available in the next couple of days, I'll try to add that functionality.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]