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.