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

xuexixuexi

学习了

学习了