Monitoring diskspace usage 1

[ddrillich]

Good Day,

Our DB grows quite quickly. Is there a way to see the instance usage of diskspace along time? Meaning, we would like to see for example how much diskspace of the total datafiles was used last Monday versus this Monday.

We use 8.1.7 and planning to upgrade to 9i this year.

Any thoughts?

Regards,
Dan

 

[SantaMufasa]

Dan,

You have probably seen my "Freespace.sql" script (probably appearing in all three of our Tek-Tips Oracle Fora). By running a variant of that code into an INSERT statement (that includes the SYSDATE), you can gather the historical values for your tablespace consumptions.

Does this give you enough insight to build something, or do you need a specific proof-of-concept example. If you need the example, please post sample output that you wish to see.

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]

 

[ddrillich]

Hi Dave,

The Search feature on tek-tips needs some improvement, unless I miss the obvious. Can you please give me a link the one of these threads?

Regards,
Dan

 

[SantaMufasa]

Dan,

Better yet, why don't I just repost the "freespace.sql" script code and output:

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

                                                                                         % 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,381,056    2,018,770,944     96 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF'
DATA2              5    2,097,152,000      241,172,480        6,684,672    2,090,467,328     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           49,152    2,097,102,848     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      782,237,696      282,058,752   10,203,701,248


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

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

You can "graze" the code for the pieces of information that you want in your regularly scheduled (e.g., weekly) run that harvests disk-consumption information from your Oracle instance. Once you are satisfied with the data components of your SELECT, you can simply prepend the SELECT with an "INSERT INTO DISK_CONSUMPTION SELECT..."

Let us know if this provides a light at the end of the tunnel.

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]

 

[ddrillich]

Thank you Dave!

Is there away to also see the information about, let's say last Monday side by side this Monday?

Regards,
Dan

 

[SantaMufasa]

Sure, Dan...that will be a function of your report SELECT query that accesses your historical-consumption table. If you can post the table that you devise to store historical consumption, along with a few representative rows of hypothetical consumption data from the table, then we can produce a SELECT that makes side-by-side comparisons.

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]

 

[ddrillich]

Hi Dave,

Freespace.sql worked as advertised!! Thanks!
Got the idea about the historical report.

Regards,
Dan