ss

Oracle Tips by Burleson

Reporting on the Oracle Disk Architecture

If your shop follows the OFA standard, you can write a dictionary query that will report on the disk-to-file mapping for your database. This script assumes that you use OFA names for your datafiles (e.g., /u02/oradata/xxx.dbf), and that your UNIX mount points map to easily identifiable physical disks. The script below queries the dba_data_files view and reports the mapping.

rpt_disk_mapping.sql
set pages 999;
set lines 80;

column mount_point heading 'MP';

break on mount_point skip 2;

select
substr(file_name,1,4) mount_point,
substr(file_name,21,20) file_name,
tablespace_name
from
dba_data_files
group by
substr(file_name,1,4),
substr(file_name,21,20) ,
tablespace_name
;
Here is the output from this script. Please note that there is a one-to-one correspondence between Oracle tablespaces, physical datafiles, and UNIX mount points.

MP FILE_NAME TABLESPACE_NAME
---- -------------------- ------------------------------
/u02 annod01.dbf ANNOD
arsd.dbf ARSD
bookd01.dbf BOOKD
groupd01.dbf GROUPD
pagestatsd01.dbf PAGESTATSD
rdruserd01.dbf RDRUSERD
subscrd01.dbf SUBSCRD
system01.dbf SYSTEM
userstatsd01.dbf USERSTATSD

/u03 annox01.dbf ANNOX
bookx01.dbf BOOKX
groupx01.dbf GROUPX
pagestatsx01.dbf PAGESTATSX
perfstat.dbf PERFSTAT
rbs01.dbf RBS
rdruserx01.dbf RDRUSERX
subscrx01.dbf SUBSCRX
temp01.dbf TEMP
tools01.dbf TOOLS
userstatsx01.dbf USERSTATSX

The above is an excerpt from the "Oracle9i UNIX Administration Handbook" by Oracle press, authored by Donald K. Burleson.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA