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
That's great
ReplyDelete#####|ORACLE_DB|Display all datafiles, tempfiles and logfiles (and their sizes)|######
ReplyDeleteset 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
/
####Oracle_DB||How large is the database||#####
ReplyDeletecol "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
/
Good One ranvir
DeleteThanks Ajay :) Keep visiting for useful information.
ReplyDeleteNice one Nimish- Thanks
ReplyDeleteThank you for sharing. It was a great help for starters.
ReplyDeleteThanks.
ReplyDeleteThanks
DeleteThanks
Deletethax
ReplyDeleteVery helpful.. Thank you.. Satyam Kumar
ReplyDeleteThank you buddy..!!! Really very helpful!!!! keep it up!!!
ReplyDeletebravo .
ReplyDeleteThank Nimish!!!!
ReplyDeleteWould be so nice to post something that works,- for a change
ReplyDeleteActually 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.
ReplyDeletethanks
ReplyDeleteThanks :)
ReplyDeleteIs it the query for size of the database available or occupied?
ReplyDeletethanks, very useful for quick view of database
ReplyDeletegreat info
ReplyDeletethanks
Thak you for the auspicious writeup. It in fact was a amusement account it.
ReplyDeleteLook advanced to far added agreeable fromm you! However, how can we communicate?
Hello there, You have done an excellent job.
ReplyDeleteI'll certainly digg it and personally suggest to my friends.
I am confident they'll be benefited from this website.
I'd like to find out more? I'd love to find out some additional information.
ReplyDeleteHighly energetic post, I loved that a lot. Will there be a part 2?
ReplyDeleteI'm no longer positive where you are getting your information, however great topic.
ReplyDeleteI must spend a while learning more or understanding more.
Thanks for wonderful info I was searching for this information for my mission.
I constantly spent my half an hour to read this website's posts all the
ReplyDeletetime along with a mug of coffee.
Keep on writing, great job!
ReplyDeleteGreat... dba_segments gives the occupied oracle database size
ReplyDeleteGet 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.
ReplyDeleteSite URL: https://sqloptimize.com
I need to include DB name as well in the size query. Any pointers how it can be done
ReplyDelete