DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Quesion on a cron shell script

  1. #1
    Join Date
    Oct 2000
    Posts
    76
    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
    J.T.

  2. #2
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width