SQL Script

Database Layout

Updated : 06-June-2000
Version : 1.0

Description

This query lists the physical location of datafiles, controlfile and redologs contained within a database. It can help is you have a disk bottleneck to decide where to move files around.

Parameters

&1 - Table Owner

SQL Source

set pages 200

 column tablespace_name format a25
 column file_name format a40
 col Mb format 9,999 heading "Size (Mb)"

spool dblayout.lst

 select file_name,tablespace_name,bytes/1024/1024 Mb
 from dba_data_files
      union all
 select  member file_name,'log group '||a.group# tablespace_name
             ,b.bytes/1024/1024 Mb
 from v$logfile a,v$log b
 where a.group#=b.group#
      union all
 select name file_name,'control file' tablespace_name,0 Mb
 from v$controlfile
 order by 1,2;

spool off