Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: sending mail

  1. #1
    Join Date
    Jun 2010
    Posts
    81

    Unanswered: sending mail

    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

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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:
    Code:
    mailx -s "subject" email@someone.com <text_file

  3. #3
    Join Date
    Jun 2010
    Posts
    81

    executing script within sqlplus

    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;

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Code:
    $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.

  5. #5
    Join Date
    Jun 2010
    Posts
    81
    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

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1

  7. #7
    Join Date
    Jun 2010
    Posts
    81
    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

  8. #8
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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:
    Code:
    EXIT;
    EOF
    
    mailx -s "Tablespace for $ORACLE_SID" name@company.com</tmp/tablespace_report.txt
    
    fi

  9. #9
    Join Date
    Jun 2010
    Posts
    81
    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
    Last edited by newdbaxchange; 11-05-10 at 11:56.

  10. #10
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Why did you remove the <<EOF from line 11.

  11. #11
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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:
    Code:
    if..............
    sqlplus / as sysdba <myreport.sql
    mailx ..............
    fi

  12. #12
    Join Date
    Jun 2010
    Posts
    81
    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

  13. #13
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    You should create a short script that just does:
    Code:
    #!/bin/ksh
    env >/tmp/myreport.log
    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.

  14. #14
    Join Date
    Jun 2010
    Posts
    81
    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

  15. #15
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    The problem is with the export statements.
    Change them to:
    Code:
    ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1:/u01/app/oracle/product/10.2.0/db_1; export ORACLE_HOME
    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 LD_LIBRARY_PATH
    etc
    We generally redefine LD_LIBRARY_PATH as:
    Code:
    LD_LIBRARY_PATH=newpath:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
    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.
    Last edited by kitaman; 11-09-10 at 10:29.

Posting Permissions

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