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

32 comments:

  1. #####|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
  2. ####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
  3. Thanks Ajay :) Keep visiting for useful information.

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

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

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

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

    ReplyDelete
  8. 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
  9. Is it the query for size of the database available or occupied?

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

    ReplyDelete
  11. Thak you for the auspicious writeup. It in fact was a amusement account it.
    Look advanced to far added agreeable fromm you! However, how can we communicate?

    ReplyDelete
  12. Hello there, You have done an excellent job.
    I'll certainly digg it and personally suggest to my friends.
    I am confident they'll be benefited from this website.

    ReplyDelete
  13. I'd like to find out more? I'd love to find out some additional information.

    ReplyDelete
  14. Highly energetic post, I loved that a lot. Will there be a part 2?

    ReplyDelete
  15. I'm no longer positive where you are getting your information, however great topic.

    I must spend a while learning more or understanding more.
    Thanks for wonderful info I was searching for this information for my mission.

    ReplyDelete
  16. I constantly spent my half an hour to read this website's posts all the
    time along with a mug of coffee.

    ReplyDelete
  17. Keep on writing, great job!

    ReplyDelete
  18. Great... dba_segments gives the occupied oracle database size

    ReplyDelete
  19. Get Instant help from our expert for your Database issues or to Optimize your SQL Script. We will be charging on a hourly basis. You can initiate the chat with our expert for the best and fast solution.

    Site URL: https://sqloptimize.com

    ReplyDelete
  20. I need to include DB name as well in the size query. Any pointers how it can be done

    ReplyDelete