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.
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"
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
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.
hack spreport.sql and pass values using &1 and &2 as positional sqlplus parameters
Click Here to Expand Forum to Full Width