Oracle: Total Size of The Database

An oracle database consists of data files, redo log files, control files, temporary files.
The size of the database actually means the total size of all these files.

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual


Related Links:
- Oracle DBA Daily Checklist
- Database Routine Checklist Queries
- Find 10 largest objects

24 comments:

  1. Replies
    1. Thanks Ajay :) Keep visiting for useful information.

      Delete
  2. #####|ORACLE_DB|Display all datafiles, tempfiles and logfiles (and their sizes)|######

    set lines 100 pages 999
    col name format a50
    select name, bytes
    from (select name, bytes
    from v$datafile
    union all
    select name, bytes
    from v$tempfile
    union all
    select lf.member "name", l.bytes
    from v$logfile lf
    , v$log l
    where lf.group# = l.group#
    union all
    select name, 0
    from v$controlfile) used
    , (select sum(bytes) as p
    from dba_free_space) free
    /

    ReplyDelete
  3. ####Oracle_DB||How large is the database||#####

    col "Database Size" format a20
    col "Free space" format a20
    col "Used space" format a20
    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
    /

    ReplyDelete
  4. Nice one Nimish- Thanks

    ReplyDelete
  5. Thank you for sharing. It was a great help for starters.

    ReplyDelete
  6. Very helpful.. Thank you.. Satyam Kumar

    ReplyDelete
  7. Thank you buddy..!!! Really very helpful!!!! keep it up!!!

    ReplyDelete
  8. Would be so nice to post something that works,- for a change

    ReplyDelete
  9. Actually size of the Oracle database is little bit tricky question. Total size of the database is right, but comes to actual size then we can get it using dba_data_files, and the used space can be calculated from dba_sagments. Thanks for solution.

    ReplyDelete
  10. Is it the query for size of the database available or occupied?

    ReplyDelete
  11. thanks, very useful for quick view of database

    ReplyDelete