Database size - script

[gatetec]

I am in need of a script that can give me:

Database size allocated in GB
Database size used in GB
Database growth over certain periods (i.e. 30 days, etc) in GB

thx much

 

[SantaMufasa]

Gatetec, First, welcome to our "Happy Little Oracle Family".

Second, following is my SQL*Plus "freespace.sql" script, which discloses the consumption of your database, both in terms of current file sizes, actual space consumptions within those files, and potential growth of those files (provided you have available the dist resources). Specifically, the script produces these categorical results: File name, File number, Total Potential File Size (regardless of whether the file is in AUTOEXTEND mode or note), Total Current File Size, Number of Bytes Used, Potential Freespace (i.e., potential size minus bytes used), Percent of potential-size that is free, Autoextensibility Flag, and File Name.

Section 1 -- "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 -- Output from "Freespace.sql" (appropriately divided to accommodate Tek-Tips screen-width limitations):

                                Total            Total                  
Tablespace                  Potential          Current                  
Name               #        File Size        File Size       Bytes Used
--------------- ---- ---------------- ---------------- ----------------
DATA1              4    2,097,152,000      104,857,600       37,027,840
RBS                2    2,097,152,000       73,400,320       27,394,048
SYSTEM             1    2,097,152,000      142,606,336      135,118,848
TEMP               3    2,097,152,000       10,485,760        1,073,152
TFQLTY             5    2,097,152,000       73,400,320       65,601,536
                     ---------------- ---------------- ----------------
sum                    10,485,760,000      404,750,336      266,215,424

(The output, below, are continuations of the output lines, above):

                 % Free
                     of
                   Pot.
       Potential  Total Auto
      Bytes Free  Bytes Ext. Filename
---------------- ------ ---- -------------------------------------------
   2,060,124,160     98 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTDATA01.DBF'
   2,069,757,952     98 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTRBS01.DBF'
   1,962,033,152     93 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTSYS01.DBF'
   2,096,078,848     99 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTTEMP01.DBF'
   2,031,550,464     96 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTTFQLTY.DBF'
----------------
  10,219,544,576

Once you copy, paste, and run the code from Section 1, above, let me know if it resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]