Oracle all file info and Database size 

Oracle file info 

select 'all_data_files' file_type,  round(sum(bytes)/1024/1024) MB from  v$datafile
union all
select 'all_temp_files',round(sum(bytes)/1024/1024) from  v$tempfile
union all
select 'all_log_files', round(sum(bytes)/1024/1024) from  v$log l, v$logfile f where  f.group#=l.group#
union  all
select 'all_control_files', round(sum((select ceil(2 * sum(record_size * records_total)) from v$controlfile_record_section))/1024/1024) from v$controlfile
order by 1
/

FILE_TYPE                 MB
----------------- ----------
all_control_files         18
all_data_files          1195
all_log_files            150
all_temp_files            20


set linesize 200
col name format a80
select  name,'data_file' file_type, round(bytes/1024/1024) MB from   v$datafile
union all
select name, 'temp_file',round(bytes/1024/1024) from  v$tempfile
union all
select   f.member, 'log_file', round(l.bytes/1024/1024) from v$log l, v$logfile f where   f.group#=l.group#
union all
select   name, 'control_file', (select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section) from v$controlfile
order by 2


NAME                                                                             FILE_TYPE            MB
-------------------------------------------------------------------------------- ------------ ----------
/u01/oracle/oradata/vihaan/control02.ctl                                         control_file          9
/u01/oracle/oradata/vihaan/control01.ctl                                         control_file          9
/u01/oracle/oradata/vihaan/undotbs01.dbf                                         data_file            30
/u01/oracle/oradata/vihaan/users01.dbf                                           data_file             5
/u01/oracle/oradata/vihaan/sysaux01.dbf                                          data_file           480
/u01/oracle/oradata/vihaan/system01.dbf                                          data_file           680
/u01/oracle/oradata/vihaan/redo01.log                                            log_file             50
/u01/oracle/oradata/vihaan/redo03.log                                            log_file             50
/u01/oracle/oradata/vihaan/redo02.log                                            log_file             50
/u01/oracle/oradata/vihaan/temp01.dbf                                            temp_file            20

10 rows selected.



SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';

Init F
------
SPFILE



col NAME format a30
col value format a10
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest_size'

NAME                           VALUE
------------------------------ ----------
db_recovery_file_dest_size     2147483648




Database size


select        round((( select sum(bytes) from v$datafile )
                 + ( select sum(bytes) from v$tempfile )
                 + ( select sum(bytes) from v$log l,v$logfile f where f.group#=l.group#)
                 + ( select sum((select ceil(2 * sum(record_size * records_total))
                 from v$controlfile_record_section)) from v$controlfile)) / 1024 / 1024) "dbsize(MB)"
from           dual
/ 

dbsize(MB)
----------
      1383