How to calculate oracle database size

Written by andy carr
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to calculate oracle database size
Database sizes include physical disk space. (Jupiterimages/ Images)

Over time Oracle databases grow incrementally in size. Depending on the size of the organisation the growth factor can be such that maintenance needs to be done at periodic intervals, to ensure enough space is available on the server and the database continues to perform efficiently. An Oracle database size depends on a number of factors. In addition to data, there are temporary files and essential control files containing the structure of the database. These are all essential for the database to work.

Skill level:
Moderately Easy

Other People Are Reading


  1. 1

    Include the data file sizes in the query. The total size of the database includes tables, fields, stored procedures and other database objects. Calculate size from the "dba_data_files" view, as follows:

    SELECT SUM(bytes)/1024/1024/1024 data_size from dba_data_files;

  2. 2

    Calculate the size of temporary files. Temporary files store data during processing, but is not permanent. Calculate the temporary file size as follows:

    select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files.

  3. 3

    Obtain the size of the redo log. The redo log saves any changes in the database before they are applied to the actual database data. This provides a way to restore the database to its original state prior to a statement designed to amend any data.

    The size of the redo log can be obtained with the following statement:

    select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log.

  4. 4

    Extract the size of the control file used by Oracle, using the V$CONTROLFILE view. The control file view is used to obtain information on database schema and the objects contained within.

    The select statement to obtain the size of the control file is:

    select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile

  5. 5

    Combine the select statements to calculate the database size. The output corresponds to the total size of the database in gigabytes:

    select d.data_sizeT.temp_sizeR.redo_sizeC.controlfile_size "Database Size in GB" from ( select sum(bytes)/1024/1024/1024 data_size

    from dba_data_files) d,

    ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size

    from dba_temp_files ) t,

    ( select sum(bytes)/1024/1024/1024 redo_size

    from sys.v_$log ) r,

    ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size

    from v$controlfile) c;

    This will output the total size of the database in the following format:

    Database Size in GB


Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.