How to install Statspack in Oracle

Our Technical Support Portal has changed as we have moved to a new platform. Please visit support.quark.com to access your support tickets or to log a new ticket with us. All our existing customers should look for an email from support@quark.com for activation of their New Support Account. If you face any difficulty in accessing the new portal, please write to us at support@quark.com

How to install Statspack in Oracle

Use these steps to install Statepack

  1. Execute the command : export Oracle_SID=qdmsprod
  2. Login into Oracle with the sys user.
  3. Create a tablespace Statspack with a size of approximately 150-250 Mb. This is used for collecting the database statistics.

    SQL>create tablespace statspack datafile ‘<datafile-path>/stats01.ora’ size 250m;
  4. Install Statspack. Execute spdrop.sql if statspack is already installed.

    SQL> @<oracle_home>/rdbms/admin/spdrop.sql
    Execute spcreate.sql to install statspack
    SQL> @<oracle_home>/rdbms/admin/spcreate.sql


    Enter the following values for the different items it prompts:

i) Password for the user perfstat – quarkdms
ii) Default Tablespace – statspack
iii) Temporary Tablespace – Any Temporary tablespace of the database

  1. Connect with the user perfstat:

    SQL> conn perfstat/quarkdms
    Execute the script spauto.sql to automate the collection of statspack statistics every hour
    SQL>@<oracle_home>/rdbms/admin/spauto.sql

Important Note: Please note the Job ID of the job.

Was this answer helpful?YesNo
Topic Information
  • Topic #: 30026-1850
  • Date Created: 11/20/2012
  • Last Modified Since: 11/20/2012
  • Viewed: 1893
Welcome Guest