On Thu, Aug 29, 2002 at 04:58:22AM -0800, Erik Williams wrote:
> I have the collections of snapshots scheduled in DBMS_JOBS, but I was
> looking for a way to automate running of the reports from these snapshots.
> It looks as though there is no easy way to do this.
spreport.sql is an interactive script, but you could re-write a local
copy that queries the snap_ids of interest and feeds it to the rest of
the code cutting out the interactive part. That seems like a pretty
easy idea. Oracle will likely change statspack thus hurting your local
copy one day.
I used expect to do this because tcl is easy and Expect is a tcl
extension for automating interactive applications. This comes in handy
for lots of login stuff on the fly. While my script is not fault tolerant, it
works and took about 15 minutes to throw together, been running with it
a real long time now. I'll go back and fix it up someday...miracles can
happen.
Here is my little q&d:
#!/usr/local/bin/tclsh
# - run local statspack report for last two snaps
# - mail report output
package require Expect
set rwd "/home/stellr/statspack"
## start sqlplus session
set cmd "/db03/app/oracle/product/8.1.7/bin/sqlplus /nolog"
eval spawn $cmd
expect "SQL>"
send "connect statspackid/statspackpw\r"
expect "SQL>"
## query the snap_ids
send "set feedback off;\r"
expect "SQL>"
send "select snap_id from stats\$snapshot;\r"
expect "SQL>"
set snapids $expect_out(buffer)
## set vars for snap_id numbers from the query above
set snapl [lindex $snapids [expr [llength $snapids] - 2]]
set snapp [lindex $snapids [expr [llength $snapids] - 3]]
set rname "$rwd/sp_${snapp}_$snapl"
## run the locally modified spreport
send "@$rwd/spreport.local.sql\r"
expect "begin_snap:"
send "$snapp\r"
expect "end_snap:"
send "$snapl\r"
expect "report_name:"
send "$rname\r"
expect "SQL>"
send "exit\r"
expect "\$"
## mail report to the usual suspects
exec cat $rname.lst | /usr/bin/mailx -s "statpack report - [exec date]"
[EMAIL PROTECTED]
exit
===============================================================
Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC 28^D
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ray Stell
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
