SQL Script Database Layout

Updated : 30-Jul-2002
Version : 2.1

Description

This query lists the physical location of datafiles, controlfile , online redologs and archive redo logs 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 a15
column file_name format a56
col Mb format 9,999 heading "Mb"

spool dblayout.lst

select   value, name,null
from     v$parameter
where    name like 'log_archive_dest%'
union all
select file_name,tablespace_name,bytes/1024/1024 Mb
from dba_data_files
union all
select file_name,tablespace_name,bytes/1024/1024 Mb
from dba_temp_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

Previous Oracle Version Links

Database Layout

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal