Hi,

I am not very good at shell scripting, so I am hoping for some assistance here. Firstly I went to send a mail message and it just hangs - this is not in a script but from the command prompt. Why does it hang until I issue a ctl_d or ctl_c? What would I need to do so it drops back to command line?

$ mailx -s "Tablespace Report from ABC" name.surname@host.com

Because the mailx command is expecting the body of the email to be entered from the keyboard until a <ctrl>d is entered on column 1 of the input.
Otherwise create the body of the email in a text file and use:

Hi,

the mail part works fine, however I am not sure now how to execute sql statements interactively - if you could shed some light

for example - I would like to perform something similar to the following :-

export ORACLE_SID=SIDNAME
sqlplus '/ as sysdba'
set wrap off
spool db
select * from v$database;
spool off
exit
mailx -s ...... <filename
exit;

If that is the correct interpretation then, replace filename with the name of the file that 'spool db' creates.

Hi,

thanks for your feedback, however I was giving an example in the previous message.

This is my script below

Basically all I want to do is spool the file prior to gathering the tablespace details. Spool off
after it has completed and send an email. However I am not sure how to enter into sqlplus
as sysdba and execute this script and then exit, from a shell script. I can then include
this script into cron for automation

================================================== ========

clear breaks
clear computes
clear columns
set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/
===========================================

If anyone can help that would be appreciated. This syntax works perfect for gathering tablespace information

ORACLE SQL*Plus: An Introduction and Tutorial

Hello if you can help then please do so - else just ignore the mail and move on. No need to post rude replies or suggesstions to go and read the manual, which are unhelpful.

I have a shell script which collects the tablsepace information. If I run the SQL it work perfectly fine. However when I put in a script to run and mail the spool file at the end - nothing happens. It just drops back to the prompt. No file is generated and therefore no mail is sent. Can someone with better unix skills than myself have a quick glance and
make some suggesstions as to what I can do next or what I am doing wrong.

Many thanks

here is the script >>

#!/usr/bin/ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=.:/usr/bin:/export/home/oracle/bin:/u01/app/oracle/product/10.2.0/db_1/bin:/usr/sbin:/usr/bin:/bin:/usr/local/bin:/usr/sfw/bin
export ORACLE_SID=smtprd1
export HOME=/u01/app/oracle/admin/dba/log

cd $HOME
if [[ $count -gt 0 ]];then
sqlplus / as sysdba << EOF
SET FEEDBACK OFF
SET LINESIZE 300
COLUMN alert_text FORMAT a80
clear breaks
clear computes
clear columns
set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/
SPOOL /tmp/tablespace_report.txt
/
SPOOL OFF;
EXIT;
EOF;

mailx -s "Tablespace for $ORACLE_SID" name@company.com </tmp/tablespace_report.txt
<EOF
$(cat ${/tmp}/tablespace_report.txt)
EOF
fi

Change this to:

oracle@bassmtprd1db01$ ./tbs.sh smtprd1
./tbs.sh[10]: syntax error at line 29 : `round' unexpected
oracle@bassmtprd1db01$


I made the changes and this is the reply I recieve. Again if I cut and paste the SQL I receive no errors


============= script =========

oracle@bassmtprd1db01$ more tbs.sh
#!/usr/bin/ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=.:/usr/bin:/export/home/oracle/bin:/u01/app/oracle/product/10.2.0/db_1/bin:/usr/sbin:/usr/bin:/bin:/usr/local/bin:/usr/sfw/bin
export ORACLE_SID=smtprd1
export HOME=/u01/app/oracle/admin/dba/log

cd $HOME
if [[ $count -gt 0 ]];then
sqlplus / as sysdba
SET FEEDBACK OFF
SET LINESIZE 300
COLUMN alert_text FORMAT a80
clear breaks
clear computes
clear columns
set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name
/
SPOOL /tmp/tablespace_report.txt
/
SPOOL OFF;
EXIT;
EOF

mailx -s "Tablespace for $ORACLE_SID" name@company.com</tmp/tablespace_report.txt

fi

Why did you remove the <<EOF from line 11.

You will probably find it easier if you create a file called myreport.sql containing all the lines after 'sqlplus' up to 'EOF' and change the script to:

Hi,

thanks for your feedback and I think I am getting somewhere.

I now have 2 files:- 1) just for the SQL and 2) environment variables, execution of script and to send the email

If I execute the job from the command line - no issues, it does what it's meant to do and sends an email, with no errors.
However if I schedule the job via cron, I recieve an environment variable error relating to ORACLE_BASE.

This is the script which uses the environment variables and sends an email

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=.:/usr/bin:/export/home/oracle/bin:/u01/app/oracle/product/10.2.0/db_1/bin:/usr/sbin:/usr/bin:/bin:/usr/local/bin:/usr/sfw/bin
export ORACLE_SID=smtprd1
export HOME=/u01/app/oracle/admin/dba
. oraenv <<EOF
smtprd1
EOF

sqlplus '/ as sysdba'<<EOF1
@tablespace.sql
exit
EOF1
mailx -s "Tablespace Report for $ORACLE_SID" name@company.com</tmp/lme_smart.log

The sql script is just a script which spools the file, run's the sql and spool's off

When I execute the script I am logged in as the oracle user - any ideas of how I can fix this environment variable

Here is the error message which is sent to an output file =>

/u01/app/oracle/admin/dba/LME_SMART.sh: ORACLE_BASE=/u01/app/oracle: is not an identifier

You should create a short script that just does:
Set this script up as a cron job, and look at the contents of /tmp/myreport.log.
It will tell you what the cron environment looks like. It is quite different than your login environment.
The PATH is likely different along with other variables. You have to initialize these other variables in your cron script.

Hi,

I've run a script to collect the current variables and had a look to see if there are any differences. Where they were -
I changed the main script to rectify. The header of my main script now looks like this:-
======================================

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1:/u01/app/oracle/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/10.2.0/db_1/lib:/u01/app/oracle/product/10.2.0/db_1/oracm/lib
export PATH=.:/usr/bin:/export/home/oracle/bin:/u01/app/oracle/product/10.2.0/db_1/bin:/usr/sbin:/usr/bin:/bin:/usr/local/bin:/usr/sfw/bin
export ORACLE_SID=smtprd1
export HOME=/u01/app/oracle/admin/dba/log:/export/home/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export DBA_ADMIN=/u01/app/oracle/admin/smtprd/bdump
export ORACLE_BASE=/u01/app/oracle

And yet it still fails. Again if I run from command line - all OK, but via cron will not.
I noticed when I change the first line, and place at the bottom, I recieve an error on the new first line.
Do I need to do something, like tell it to issue from ksh or bash prior to the export lines?

Thanks again - I feel I am so close to getting this to work

The problem is with the export statements.
Change them to:
We generally redefine LD_LIBRARY_PATH as:
Doing it this way covers the possibility that you have other applications that require LD_LIBRARY_PATH.

Also note that there are never spaces around the = sign.