Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Shell Script to display used space and free space of database mount points

user12097781Sep 22 2011 — edited Sep 26 2011
I am planning to write a shell script to display the used space and free space of the database mount points only. The database mount point will be retrieved from the SQL command "select distinct(substr(' '||file_name||' ',1,instr(' '||file_name||' ','/',1,3)-1)) from dba_data_files;". This command will display the output as below:

/data/d01
/data/d02

I need to append the used space and free space of the above mount points using a shell script and the output should be displayed as below:

Mounted on Used Avail Use%

/data/d01 48G 13G 80%
/data/d02 16G 44G 27%


I have written a shell script as below:

#! /usr/bin/env bash

# Use Posix output format
rm df_output.txt 2>/dev/null
df -h >df.txt
sqlplus -S "/ as sysdba"<<EOF
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP " "
spool df_output.txt
select distinct(substr(' '||file_name||' ',1,instr(' '||file_name||' ','/',1,3)-1)) from dba_data_files;
spool off
exit;
cat df_output.txt | while read line;
do
case $line in
*|df.txt)
Used_space=$(echo $line | awk '{print $2}' )
Available_space=$(echo $line | awk '{print $3}' )
USIP=$(echo $line | awk '{print $4}' )
echo "$Used_space $Available_space $USIP " >> df_output1.txt
;;
*);;
esac
done < df.txt
cat df_output1.txt

But I don't know how to append the used & free space to the mount points. Basically, I am a DBA and not a developer. Please help me on this regard.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 24 2011
Added on Sep 22 2011
5 comments
1,709 views