Estimate the database size

We have a clustered database with two nodes. My objective is to find out the size of the database. Could you please give me a script to estimate the s开发者_JAVA技巧ize of the database?

A good script is go to the dba, give a few beers and you will get what you want. If that does not help, check the v$datafile, v$tempfile and v$log views. They will give you all needed data, if you have access to them, in which case you probably are the 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
)
/

I hope this helps.

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

Use the code below to get DB size. Yes its the same as above but you can put it in a nice PL/SQL script to run 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

A slight modification to Jaun's query to include members from v$log as was pointed out and this would probably be the most accurate because it includes the controle file info which is part of the overall 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