Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Overview of STATSPACK Scripts

Don Burleson

 

Click here for STATSPACK Install directions

Overview of the STATSPACK Scripts

The STATSPACK scripts have completely changed.  All of the STATSPACK scripts are located in the $ORACLE_HOME/rdbms/admin directory.

Oracle 8.1.7 and Oracle9i Script Name

Pre Oracle 8.1.7 Script Name

Script Function

     spdoc.txt

statspack.doc

Installation documentation

     spcreate.sql

statscre.sql

Create user, tables & install packages

     spreport.sql

statsrep.sql

Standard STATSPACK report

     spauto.sql

statsauto.sql

Schedule automatic data collection

     spuexp.par

statsuexp.par

Parameter file for full STATSPACK export

     sppurge.sql

- new file -

Purge SQL for removing old snapshots

     sptrunc.sql

- new file -

Script to truncate all STATSPACK tables

     spup816.sql

- new file -

Upgrade script to moving to 8.1.6

     spup817.sql

- new file -

Upgrade script to moving to 8.1.7

     spdrop.sql

statsdrp.sql

Script to drop all STATSPACK tables

     spcpkg.sql

statspack.sql

Script to create statspack package

     spctab.sql

statsctab.sql

Creates STATSPACK tables

     spcusr.sql

statscusr.sql

Creates STATSPACK user & assigns grants

     spdtab.sql

statsdtab.sql

Drops all STATSPACK tables

     spdusr.sql

statsdusr.sql

Drops the statspack user

Next, let's take a closer look at these scripts and see details on how to install STATSPACK. Because of the differences between versions, we will have two sections: one for pre-8.1.7 and another for Oracle 8.1.7 and Oracle9i STATSPACK.

STATSPACK scripts for Oracle8 and Oracle8i

You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with "stat":

>cd $ORACLE_HOME/rdbms/admin
server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin


>ls -al stat*
-rw-r--r--   1 oracle   oinstall    1739 Dec  6  1999 statsauto.sql
-rw-r--r--   1 oracle   oinstall     843 Dec  6  1999 statscre.sql
-rw-r--r--   1 oracle   oinstall   27183 Nov 10  1999 statsctab.sql
-rw-r--r--   1 oracle   oinstall    4686 Nov 10  1999 statscusr.sql
-rw-r--r--   1 oracle   oinstall     792 Aug 27  1999 statsdrp.sql
-rw-r--r--   1 oracle   oinstall    3236 Nov 10  1999 statsdtab.sql
-rw-r--r--   1 oracle   oinstall    1081 Nov 10  1999 statsdusr.sql
-rw-r--r--   1 oracle   oinstall   26667 Dec  6  1999 statspack.doc
-rw-r--r--   1 oracle   oinstall   49821 Nov 10  1999 statspack.sql
-rw-r--r--   1 oracle   oinstall   46873 Nov 10  1999 statsrep.sql
-rw-r--r--   1 oracle   oinstall     559 Aug 27  1999 statsuexp.par

Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files in a hierarchy:

        statscre.sqlThis is the first install script run after you create the tablespace. It calls several subscripts:

statscusr.sqlThis script creates a user called PERFSTAT with the required permissions.

statsctab.sqlThis creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

statspack.sqlThis creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

        statsauto.sqlThis script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.

        statsdrp.sqlThis script is used to drop all STATSPACK entities. This script calls these subscripts:

        statsdtab.sqlThis drops all STATSPACK tables and indexes.

        statsdusr.sqlThis script drops the PERFSTAT user.

        statsuexp.parThis is an export parameter file for exporting the STATSPACK objects. This can be useful if you want to consolidate STATSPACK reports for several databases into a single STATSPACK structure.

        statspack.docThis is a generic read-me file explaining the installation and operation of the STATSPACK utility.

        statsrep.sqlThis is the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.

        statsrep80.sqlThis is a version of the STATSPACK report for Oracle 8.0.

Now that we understand the functions of each of the files, we are ready to install STATSPACK. Our first step is to review the installation files for the STATSPACK install.

STATSPACK scripts for post 8.1.6 STATSPACK

You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with "sp":

>cd $ORACLE_HOME/rdbms/admin
server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin


>ls -al sp*

-rw-r--r--    1 oracle   oinstall     1771 May 10  2001 spauto.sql
-rw-r--r--    1 oracle   oinstall    82227 May 10  2001 spcpkg.sql
-rw-r--r--    1 oracle   oinstall      877 May 10  2001 spcreate.sql
-rw-r--r--    1 oracle   oinstall    42294 May 10  2001 spctab.sql
-rw-r--r--    1 oracle   oinstall     7949 May 10  2001 spcusr.sql
-rw-r--r--    1 oracle   oinstall    69074 May 10  2001 spdoc.txt
-rw-r--r--    1 oracle   oinstall      758 May 10  2001 spdrop.sql
-rw-r--r--    1 oracle   oinstall     4342 May 10  2001 spdtab.sql
-rw-r--r--    1 oracle   oinstall     1363 May 10  2001 spdusr.sql
-rw-r--r--    1 oracle   oinstall     7760 May 10  2001 sppurge.sql
-rw-r--r--    1 oracle   oinstall   113753 May 10  2001 sprepins.sql
-rw-r--r--    1 oracle   oinstall     1284 May 10  2001 spreport.sql
-rw-r--r--    1 oracle   oinstall    26556 May 10  2001 sprepsql.sql
-rw-r--r--    1 oracle   oinstall     2726 May 10  2001 sptrunc.sql
-rw-r--r--    1 oracle   oinstall      588 May 10  2001 spuexp.par
-rw-r--r--    1 oracle   oinstall    30462 May 10  2001 spup816.sql
-rw-r--r--    1 oracle   oinstall    23309 May 10  2001 spup817.sql

Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:

        spcreate.sqlThis is the first install script run after you create the tablespace. It calls several subscripts:

        spcsr.sqlThis script creates a user called PERFSTAT with the required permissions.

        spctab.sqlThis creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

        spcpkg.sqlThis creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

        spauto.sqlThis script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.

        spdrop.sqlThis script is used to drop all STATSPACK entities. This script calls these subscripts:

        spdtab.sqlThis drops all STATSPACK tables and indexes.

        spdusr.sqlThis script drops the PERFSTAT user.

        spdoc.txtThis is a generic read-me file explaining the installation and operation of the STATSPACK utility.

        spreport.sqlThis is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.

        sprepins.sqlThis is the actual SQL that produces the STATSPACK report.

        sppurge.sqlThis is a script to delete older unwanted snapshots.

        spuexp.parThis is a export parameter file to export all of the STATSPACK data.

        sptrunc.sqlThis is a script to truncate all STATSPACK tables.

        spup816.sqlThis is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.

        spup817.sqlThis is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6.

Now, let's take a closer look at the installation procedures.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.