BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








Overview of the STATSPACK Scripts

Oracle Tips by Burleson Consulting

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



Installation documentation



Create user, tables & install packages



Standard STATSPACK report



Schedule automatic data collection



Parameter file for full STATSPACK export


- new file -

Purge SQL for removing old snapshots


- new file -

Script to truncate all STATSPACK tables


- new file -

Upgrade script to moving to 8.1.6


- new file -

Upgrade script to moving to 8.1.7



Script to drop all STATSPACK tables



Script to create statspack package



Creates STATSPACK tables



Creates STATSPACK user & assigns grants



Drops all STATSPACK tables



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”:

L 3-1

>cd $ORACLE_HOME/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.sql This is the first install script run after you create the tablespace. It calls several subscripts:

  • statscusr.sql This script creates a user called PERFSTAT with the required permissions.

  • statsctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

  • statspack.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

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

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

  • statsdtab.sql This drops all STATSPACK tables and indexes.

  • statsdusr.sql This script drops the PERFSTAT user.

  • statsuexp.par This 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.doc This is a generic read-me file explaining the installation and operation of the STATSPACK utility.

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

  • statsrep80.sql This 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.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter