A couple of weeks ago I listed a number of scripts from 18.104.22.168 relating to AWR reports – it seems only sensible to publish a corresponding list for Statspack. In fact, there are two such lists – one for “traditional” Statspack, and one for “standby statspack” – a version of statspack you can pre-install so that you can run statspack reports against a standby database.
Statspack – the basic source of information is $ORACLE_HOME/rdbms/admin/spdoc.txt
spreport.sql -> Generates a Statspack Instance report sprepins.sql -> Generates a Statspack Instance report for the database and instance specified sprepsql.sql -> Generates a Statspack SQL report for the SQL Hash Value specified sprsqins.sql -> Generates a Statspack SQL report for the SQL Hash Value specified, for the database and instance specified sppurge.sql -> Purges a limited range of Snapshot Id's for a given database instance sptrunc.sql -> Truncates all Performance data in Statspack tables spuexp.par -> An export parameter file supplied for exporting the whole PERFSTAT user
Standby Statspack – the basic source of information is $ORACLE_HOME/rdbms/admin/sbdoc.txt
sbreport.sql - Create a report sbaddins.sql - Add a standby database instance to the configuration sblisins.sql - List instances in the standby configuration sbdelins.sql - Delete an instance from the standby configuration sbpurge.sql - Purge a set of snapshots
A warning note about standby statspack – there is a significant structural change from 22.214.171.124 to 126.96.36.199 allowing support of multiple standby databases. I haven’t looked closely at it yet, but it did cross my mind that it might be possible to install the 188.8.131.52 version in an 184.108.40.206 database to avoid late upgrade issues – I may get around to testing the idea one day.