I'm not very good at scripting as my job is an DBA for a small firm, but now I'd like to implement a few cron jobs which access and report on the databases on an automatic basis.
This is my SQL TEXT
# -- 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
The SQL part works fine. However I would like to schedule this via cron, so it runs on a weekly basis at say 4am every Monday.
The mail part I believe is as follows - which I can end at the end of the script
mail -s 'Database Size for Database DB1' name@company.com < /tmp/dbsize.log
This will execute on a linux box under the Oracle user
Could someone please help and many thanks in advance
# 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
thanks for your reply but as a newbie, it's not very clear.
I was just hoping to cut and paste the results into a .sh script and execute the script. A novice like me, is unable to determine what is required or what to leave out.
Thanks for the reply, but do you think you can simplify