Database size history

Summary
Here i will demonstrate some ways to calculate the database size and space history:

Faster query!(all the files, data and temp)

SELECT SUM(MB_ALLOC)/1024 GB_DB_SIZE FROM

(SELECT SUM(ROUND(bytes_used/(1024*1024),2) + ROUND(bytes_free/(1024*1024),2)) MB_ALLOC

FROM V$temp_space_header, dba_temp_files

WHERE V$temp_space_header.file_id (+) = dba_temp_files.file_id

UNION

SELECT SUM(BYTES)/(1024*1024) MB_ALLOC FROM dba_data_files);

 

Slower query

SELECT  ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

,       ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) -

        ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Used space"

,       ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Free space"

FROM    (SELECT BYTES

        FROM    V$DATAFILE

        UNION   ALL

        SELECT  BYTES

        FROM    V$TEMPFILE

        UNION   ALL

        SELECT BYTES

        FROM    V$LOG) USED

,       (SELECT SUM(BYTES) AS P

        FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

To keep a history of how database is growing you can create a table that records for example every week the database size. The following procedure does not take into account the UNDO tablespace and TEMPORARY tablespace, only real data and indexes.

Create the table for database size history

create table db_space_hist (

        timestamp    date,

        total_space  number(8),

        used_space   number(8),

        free_space   number(8),

        pct_inuse    number(5,2),

        num_db_files number(5)

);

Create the procedure db_space_history

CREATE OR REPLACE PROCEDURE db_space_history AS

BEGIN

   INSERT INTO db_space_hist

        SELECT SYSDATE, total_space,

        total_space-NVL(free_space,0) used_space,

        NVL(free_space,0) free_space,

        ((total_space - NVL(free_space,0)) / total_space)*100 pct_inuse,

        num_db_files

 FROM ( SELECT SUM(bytes)/1024/1024 free_space

        FROM   sys.DBA_FREE_SPACE WHERE tablespace_name NOT LIKE '%UNDO%') FREE,

      ( SELECT SUM(bytes)/1024/1024 total_space,

               COUNT(*) num_db_files

        FROM   sys.DBA_DATA_FILES WHERE tablespace_name NOT LIKE '%UNDO%') FULL;

   COMMIT;

END;

/

Create the job that runs once in a week

DECLARE

  X NUMBER;

BEGIN

  SYS.DBMS_JOB.SUBMIT

    (

      job        => X

     ,what       => 'SYS.DB_SPACE_HISTORY;'

     ,next_date  => TO_DATE('22/02/2008 19:40:28','dd/mm/yyyy hh24:mi:ss')

     ,INTERVAL   => 'TRUNC(SYSDATE+7)'

     ,no_parse   => FALSE

    );

END;

Monitor how things going on periodically:

select * from db_space_hist order by timestamp desc;

Alternative:How the database size increased in GBytes per month for the last year.

SELECT TO_CHAR(creation_time, 'RRRR Month') "Month",

round(SUM(bytes)/1024/1024/1024) "Growth in GBytes"

FROM sys.v_$datafile

WHERE creation_time > SYSDATE-365

GROUP BY TO_CHAR(creation_time, 'RRRR Month');

 

Month          Growth in GBytes

-------------- ----------------

2008 December              1331

2008 November               779

2008 October                447

2009 April                  797

2009 August                 344

2009 February               505

2009 January                443

2009 July                   358

2009 June                   650

2009 March                  452

2009 May                   1787

2009 October                255

2009 September              158

 

Name	Total MB	Free MB	Used MB	Pct. Disk used
DATA	983,040	342,749	640,291	65.13%
ARCH	491,520	466,358	25,162	5.12%

 

select      NAME,

                TOTAL_MB,

                FREE_MB,

                TOTAL_MB-FREE_MB as used_MB,round(((TOTAL_MB-FREE_MB)/TOTAL_MB)*100,2)||'%' as pct_used

from v$asm_diskgroup

order by pct_used ;