Oracle Datafile info

Oracle datafile info
Oracle all file info


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
-------------------------------------------------------------------------------- ------------ ----------
/opt/app/oracle/oradata/orcl/control01.ctl control_file 9
/opt/app/oracle/flash_recovery_area/orcl/control02.ctl control_file 9
/opt/app/oracle/oradata/orcl/users01.dbf data_file 590
/opt/app/oracle/oradata/orcl/example01.dbf data_file 100
/opt/app/oracle/oradata/orcl/anujtest.dbf data_file 10
/opt/app/oracle/oradata/orcl/tsapexf01.dbf data_file 50
/opt/app/oracle/oradata/orcl/tsapexu01.dbf data_file 110
/opt/app/oracle/oradata/orcl/test.dbf data_file 20
/opt/app/oracle/oradata/orcl/rman.dbf data_file 50
/opt/app/oracle/oradata/orcl/drop.dbf data_file 2
/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf data_file 1000
/opt/app/oracle/oradata/orcl/undotbs01.dbf data_file 325
/opt/app/oracle/oradata/orcl/system01.dbf data_file 830
/opt/app/oracle/oradata/orcl/sysaux01.dbf data_file 830
/opt/app/oracle/oradata/orcl/redo05.log log_file 520
/opt/app/oracle/oradata/orcl/redo06.log log_file 520
/opt/app/oracle/oradata/orcl/redo04.log log_file 520
/opt/app/oracle/oradata/orcl/temp01.dbf temp_file 326

18 rows selected.

col VALUE format a50

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

SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest_size';


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 3917
all_log_files 1560
all_temp_files 326



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)
----------
5821




col VALUE format a50

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