DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001

    Statspack Report generation Automation

    I have a requirement to run statspack every hour on a linux box. I have done so. However additonally I have to generate a report for snaps between 6:00 p.m. and 8:00 p.m every day and send the spreport via email. Anyone has any scripts to automate generation of reports(for periodic report snaps - cronjob ??) and send via email. Any help ASAP will be greately appreciated.

  2. #2
    Join Date
    Nov 2000
    You can query the PERFSTAT tables, in order to get the snapshots and for each snapshot, issue spreport:

    select snap_id, to_number(to_char(SNAP_TIME,'HH24')) "SNAP_TM"
    from stats$snapshot
    where trunc(SNAP_TIME) = SYSDATE
    and to_number(to_char(SNAP_TIME,'HH24')) between 18 and 20

    Write a shell script, which will use this script, and direct the output to a file. Read the file, find the desired snapshots and execute spreport.

    You will need to write a PL/SQL code, which accepts begin_snap and end_snap variables and will pass them to the sprepins script.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  3. #3
    Join Date
    Nov 2001
    Thank you for the help. What I am not sure how to do is, how to pass the values for start and end statspack for the report to the spreport.sql. Normally this script will accept the values from the keys/screen. How do I pass the value to it inside a script for automation. I know how to pickup the snap_id's.

  4. #4
    Join Date
    Jun 2000
    Madrid, Spain
    hack spreport.sql and pass values using &1 and &2 as positional sqlplus parameters

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