Database size history
                                                 Last update (2010-10-15 09:52:15)
                                                                                                                   Date added (2008-02-22 19:56:53)

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

As you can see from the last query the database increased its size for the month: 2009 October 255 GBytes
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search