An Oracle Database consists of data files, redo log files, control files, temporary files.
Whenever you say the size of the database this actually means the summation of these files.
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB" from ( select sum(bytes)/1024/1024 data_size from dba_data_files ) a, ( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b, ( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c, ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d; ------------------------------------------------------------- For Round Figure For MB : select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB" from ( select round( sum(bytes)/1024/1024 ) data_size from dba_data_files ) a, ( select round( nvl(sum(bytes),0)/1024/1024 ) temp_size from dba_temp_files ) b, ( select round( sum(bytes)/1024/1024 ) redo_size from sys.v_$log ) c, ( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 ) controlfile_size from v$controlfile) d; ------------------------------------------------------------- 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; ------------------------------------------------------------- For Round Figure GB :
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from
( select round( sum(bytes/1024/1024/1024) ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes/1024/1024/1024) ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 ) controlfile_size from v$controlfile) d;
----------------------------------------------------------
Get database size from v$datafile:
select round((sum(bytes)/1048576/1024),2) from v$datafile;
------------------------------------------------------------
Get Oracle Database size from dba_data_files:
select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);
-----------------------------------------------------------------
-
here
a is megabytes allocated to ALL datafiles
b is megabytes allocated to ALL TEMP files
c is megabytes allocated to ALL redo-logs
d is megabytes allocated to ALL control files
Comments
Tags: control files, data files, database, dba_data_files, Oracle, Oracle : Size Of Database, Oracle control files, Oracle data files, Oracle dba data files, Oracle redo log files, Oracle temporary files, redo log files, Size Of Database, temporary files, v$datafile