Shell Programming and Scripting

# database_size.sh
#!/bin/bash
# source your DBA profile here which will set the sqlplus in PATH
# you can export DBAUSER/PASSWD of the database in the env ( NOT recomended) just an idea.
X=$( sqlplus -s << END
spool /tmp/dbsize.log
${DB_USER}/${DB_PASSWORD}@${ORACLE_SID}
# -- Total size of Database Size in GB
set echo off feedback off verify off pause off
SELECT 'Database Size is --> ' || TO_CHAR(ROUND((select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from v$log),0)) ||
 'GB'
FROM dual;
PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
exit
END)

mail -s 'Database Size for Database DB1' name@company.com < /tmp/dbsize.log

00 04 * * 1 database_size.sh 1>/dev/null 2>&1