Re: Schedule Statspack Report Creation

From: Ray Stell <stellr_at_cns.vt.edu>
Date: Thu, 29 Aug 2002 08:38:25 -0800
Message-ID: <F001.004C2A21.20020829083825@fatcity.com>

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]" stellr_at_cns.vt.edu exit

---

Ray Stell stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  INET: stellr_at_cns.vt.edu

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: ListGuru_at_fatcity.com (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).

Received on Thu Aug 29 2002 - 11:38:25 CDT