Estimate the size of the database

Question

We have a cluster database with two nodes. My goal is to find out the size of the database. Could you give me a script to estimate the size of the database?

Answer 1

A good script goes to dba, give some beer and you get what you want. If this does not help, check the v $ datafile, v $ tempfile and v $ log files. They will provide you with all the necessary data if you have access to it, in which case you are probably dba.

select sum(bytes)/1024/1024 MB from ( select sum (bytes) bytes from v$datafile union select sum (bytes) from v$tempfile union select sum (bytes * members) from v$log ) / 

Hope this helps.

Answer 2

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

Answer 3

Use the code below to get the size of the database. Yes, it is the same as above, but you can put it in a good PL / SQL script to work in different databases.

 SET SERVEROUTPUT ON Declare ddf Number:= 0; dtf Number:= 0; log_bytes Number:= 0; total Number:= 0; BEGIN select sum(bytes)/power(1024,3) into ddf from dba_data_files; select sum(bytes)/power(1024,3) into dtf from dba_temp_files; select sum(bytes)/power(1024,3) into log_bytes from v$log; total:= round(ddf+dtf+log_bytes, 3); dbms_output.put_line('TOTAL DB Size is: '||total||'GB '); END; / 

http://techxploration.blogspot.com.au/2012/06/script-to-get-oracle-database-size.html

Answer 4

A slight modification to the Jaun query to include members from v $ log, as indicated, and this is likely to be the most accurate since it includes controle file information, which is part of the total database size.

 select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB" from ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files) a, ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) b, ( select sum(bytes*members)/1024/1024/1024 redo_size from sys.v_$log ) c, ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) d