Script – Database structure and file location

***************************************************
Script – Database structure and file location
****************************************************

set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name format a60 heading "Control Files"

select name
from sys.v_$controlfile
/

Prompt
Prompt Redo Log File Locations >>>>
Prompt

col Grp format 9999
col member format a50 heading "Online REDO Logs"
col File# format 9999
col name format a50 heading "Online REDO Logs"
break on Grp
select group#,member
from sys.v_$logfile
/


Prompt Data Files Locations >>>>

col Tspace format a25
col status format a3 heading Sta
col Id format 9999
col Mbyte format 999999999
col name format a50 heading "Database Data Files"
col Reads format 99,999,999
col Writes format 99,999,999

break on report
compute sum label 'Total(MB)' of Mbyte on report

select F.file_id Id,
F.file_name name,
F.bytes/(1024*1024) Mbyte,
decode(F.status,'AVAILABLE','OK',F.status) status,
F.tablespace_name Tspace
from sys.dba_data_files F
order by tablespace_name;