REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_db_files_all.sql
REM
REM <SQLDIR_GRP>DBF LOG TABSP</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports all database files REDO LOG, DBF, CTL
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_db_files_all.sql
REM 
REM Example:
REM	s_db_files_all.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set pages 200
set lines 132
set trims on

col name new_value x noprint
select name from v$database;


spool s_db_files_all.&x..log

col file_name format a40 heading "Data File Name"
col tabsp_name format a15 heading "Tablespace Name"
col max_size format 999,999,999.99 heading "Max|Size MB"
col inc_by format 999,999.99 heading "Increment|By MB"
col unallocated format 999,999.99 heading "Unallocated|MB"
col cur_size format 999,999,999.99 heading "Current|Size MB"


break on mount_point skip 1
col mount_point format a8 heading "Mnt"
compute sum of max_size cur_size unallocated on mount_point

select  SUBSTR(fn.name,1,DECODE(INSTR(fn.name,'/',2),0,INSTR(fn.name,':',1),INSTR(fn.name,'/',2))) mount_point
,       tn.name						   tabsp_name
,	fn.name						   file_name
,	ddf.bytes/1024/1024				   cur_size
,	decode(fex.maxextend,
                NULL,ddf.bytes/1024/1024
                    ,fex.maxextend*tn.blocksize/1024/1024) max_size
,       nvl(fex.maxextend,0)*tn.blocksize/1024/1024 -
        decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024)   unallocated
,	nvl(fex.inc,0)*tn.blocksize/1024/1024		   inc_by
from	sys.v_$dbfile	fn
,	sys.ts$		tn
,	sys.filext$	fex
,	sys.file$	ft
,	dba_data_files	ddf
where	fn.file# = ft.file#
and	fn.file# = ddf.file_id
and	tn.ts# = ft.ts#
and	fn.file# = fex.file#(+)
order by 1
/

col redo_logs format a45

select	group#, status, member redo_logs
from	v$logfile
/

select * from v$log
/

col control_files format a45

select 	name control_files
from	v$controlfile
/

spool off