-
I have a shell script that checks for freespaces for three databases on the same machine. The cron entry and actual shell script are attached below. My problem is: if I run the script from prompt line, I will get different results for all three databases. But if I schedule it from cron, the run will give identical results, i.e. the results from MPTRAIN for all three databases. I can't figure out why. Please help. Thanks.
----------------------------------------------------
0 11 * * * /usr/oracle/admin/MPTRAIN/scripts/chkfree.ksh
#!/bin/ksh
set -x
SPATH=/usr/oracle/admin/MPTRAIN/scripts;export SPATH
PATH=/opt/bin:/usr/bin:/bin:/sbin:/usr/sbin:/usr/oracle/product/8.1.6/bin;export PATH
ORACLE_SID=MPPROD;export ORACLE_SID
LOGPATH=/usr/oracle/admin/ECOM/logs;export LOGPATH
$ORACLE_HOME/bin/sqlplus -s <
username/pwd@mpprod
@$SPATH/freespaces.sql
!
mailx -s " MPPROD: TS Freespace " myemail.com < $SPATH/freespaces.txt
ORACLE_SID=MPDEV;export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s <
username/pwd@mpdev
@$SPATH/freespaces.sql
!
mailx -s " MPDEV: TS Freespace " myemail.com < $SPATH/freespaces.txt
ORACLE_SID=MPTRAIN;export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s <
username/pwd@mptrain
@$SPATH/freespaces.sql
!
mailx -s " MPTRAIN: TS Freespace " myemail.com < $SPATH/freespaces.txt
--------------------------------------------------------------
freespaces.sql:
spool freespaces.txt
select dd.tablespace_name,
(sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
sum(distinct('.'||lpad(dd.file_id,10,'0'))))/1048576
"Total (MB)",
sum(nvl(df.bytes,0))/1048576 "Free (MB)",
max(nvl(df.bytes,0)) "Max Free (Byte)",
trunc(10000*sum(nvl(df.bytes,0)) /
(sum(distinct(dd.bytes||'.'||lpad(dd.file_id,10,'0'))) -
sum(distinct('.'||lpad(dd.file_id,10,'0')))))/100 "% Free"
from
dba_free_space df,
dba_data_files dd
where dd.tablespace_name = df.tablespace_name (+)
and dd.file_id = df.file_id (+)
group by dd.tablespace_name;
spool off
-
hi, jt. i'm not the best person to answer this, but i'll try. the difference is probably in your environment variables. in particular, see if TWO_TASK is set. maybe run a cron job to capture all your variables, if you can't track down the defaults for the server.
when you use
ORACLE_SID=MPTRAIN;export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s < username/pwd@mptrain
it shouldn't matter what the ORACLE_SID is. the "@string" will override it by connecting you via Net8. if these databases are all on this server, trying running the script with
ORACLE_SID=MPTRAIN;export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s < username/pwd
see if this gives you different results
