ba6.us - Dave's Database Related Stuff

#!/usr/bin/bash
#---------------------------------------------------------------------------
# Written by  : David Mann @ http://ba6.us
# Script Name : alldatafiles.sh
# Description : Provides list of all datafiles for each running database on 
#               on a machine
#---------------------------------------------------------------------------

ORATAB=/etc/oratab
echo "INSTANCE_NAME, FILE_NAME"

# Step through running instances
ps -ef | grep ora_smon_ | grep -v grep | cut -b61-70 | while read LINE
do
    # Assign the ORACLE_SID
    ORACLE_SID=$LINE
    export ORACLE_SID

    #Find ORACLE_HOME info for current instance
    ORATABLINE=`grep $LINE $ORATAB`
    ORACLE_HOME=`echo $ORATABLINE | cut -f2 -d:`
    export ORACLE_HOME
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
    export LD_LIBRARY_PATH

    # Put $ORACLE_HOME/bin into PATH and export.
    PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc ; export PATH

    # Get SGA
    sqlplus -s "/ as sysdba" <<EOF
    SET HEADING OFF
    SET FEEDBACK OFF
    SET LINESIZE 3800
    SET TRIMSPOOL ON
    SET TERMOUT OFF
    SET SPACE 0
    SET PAGESIZE 0
    select (select instance_name from v\$instance) as DB_NAME,
           file_name
      from dba_data_files
     order by 2;
EOF

done