Title: Mapping Physical UNIX Devices and Oracle Datafiles

Author: Darlynna Swarts, a DBA for AT&T CBS in Somerset, New Jersey.

I wanted to share a few quick and dirty Unix/Oracle scripts/reports I wrote which have proved useful for me while planning db
reorg. The following Unix/Oracle scripts map physical Unix devices to Oracle datafiles information, using Unix and Oracle
dba_data_files. A table is created and loaded with Unix device information. Then a report is run to map the Unix and Oracle
datafiles information.

File Descriptions

     df_run.sh -- unix shell to execute gen_tab_disk_sql.sh, ldr_tab_disk.sh and generate unix controller, disk and filesystem
     info
     gen_tab_disk_sql.sh -- invokes sqlplus to create physical table--you will need to edit this to store in your own schema
     ldr_tab_disk.sh -- invokes sqlldr to load Unix device information (generated in df_run.sh) into the physical table
     ldr_tab_disk.ctl -- sqlldr control file--you will also need to edit this to execute from your own schema
     df_map.sql -- invoked from sqlplus to generate physical map

###################################################################
#! /bin/ksh                                                       #
#df_run.sh                                                        #
#Darlynna Swarts ATT CBS DBA Group, Somerset, NJ  08873#
###################################################################
#set -o xtrace
usage ()
{
   print "USAGE:  $1 [-v]"
   exit
}
version()
{
   print "Version:  df_run.sh 1.1:  11/11/97"
   exit
}
set -- 'getopt vV $*'
if [ $? != 0 ]; then
   usage $0
fi
for Z in $*; do
   case $Z in
      -v)
           version $0
           ;;
      -V)
           version $0
           ;;
      --)
           shift; break
           ;;
   esac
done
tput sgr0; tput clear
###################################################################
# gen_tab_disk_sql.sh
# sh to exec SQL to create table to store disk data               #
###################################################################
sqlplus -s / << EOF > ./gen_tab_disk_sql.out
set echo on
set verify on

drop table dswarts.physical_tab
;
create table dswarts.physical_tab
(
file_system varchar2(30) constraint pk_physical primary key,
cntrl_disk varchar2(30) not null,
t_os_blocks number(12),
t_os_blocks_used number(12)
)
;
set echo off
set verify off
exit
EOF
###################################################################
# ldr_tab_disk.ctl
# Collect Unix device information          #
###################################################################
echo "begindata" > ./df.out
df -k|tr -s ' '|cut -d' ' -f1,2,3,6 > ./df.out

###################################################################
# ldr_tab_disk.sh
# sh to execute SQLLOAD to load disk data to table                   #
###################################################################
if [ -s ldr_tab_disk.sh ]; then
   echo "\nLoading Physical table for ${ORACLE_SID}"
   ldr_tab_disk.sh
else
   echo "\nScript to load table not in current directory"
   echo "\nor ORACLE_SID not set."
   echo "\n${ORACLE_SID}"
fi
###################################################################
# df_map.sql
###################################################################
REM  This SQLPLUS script will generate a report to physically map
REM  Unix device information and Oracle datafiles.

set echo off feed off pages 24 pause on
set pause 'Please press enter for next page '
col "Controller and Disk" for a20
col "File System" for a20
col "File Location and Name" for a30
break on "Controller and Disk" skip 1
ttitle "Physical Map for Unix Device Information and Oracle Datafiles"

select p.cntrl_disk "Controller and Disk",
       p.file_system "File System",
       substr(df.file_name,to_number(length(p.file_system)+1),
          length(df.file_name)) "File Location and Name"
from   physical_tab p, dba_data_files df
where  p.file_system||'/' =
         substr(df.file_name,1,to_number(length(p.file_system)+1))
and    p.file_system <> '/'
order  by p.cntrl_disk,p.file_system,df.file_name
/
ttitle ""
set echo on feed on pause off