Infrastructure at your Service

Franck Pachot

Statspack installation scripts

By June 29, 2018 Oracle 13 Comments

By Franck Pachot

When Diagnostic Pack is disabled, either because you don’t have Diagnostic Pack or you are in Standard Edition, I highly recommend to install Statspack. When you will need it, to investigate on an issue that occured in the past, you will be happy to have it already installed and gathering snapshots.

I order to be sure to have it installed correctly, there’s a bit more to do than just what is described in spcreate.doc and I detail that in a UKOUG Oracle Scene article Improving Statspack Experience.

For easy download, I’ve put the scripts on GitHub:

You will find the following scripts for Statspack installation:

  • 01-install.sql to create the tablespace and call spcreate
  • 02-schedule.sql to schedule snap and purge jobs
  • 03-idle-events.sql to fix issue described at
  • 04-modify-level.sql to collect execution plans and segment statistics

And some additional scripts:

  • 08-create-delta-views.sql to create views easy to query
  • 11-comment-with-load.sql to add load information to snapshots without comments

You can leave feedback and comment on Twitter:


  • Grzegorz Goryszewski says:

    Thanks for all the valuable info.
    As far as I can tell during the sp installation dbms_job package was recreated, that could lead to some disturbance in production. Not sure if still the case thoug.

  • Jan says:

    Thank you for publishing your scripts. You’ve implemented some special aspects that I didn’t consider, yet.

    One question: Why do you schedule one purge job per RAC instance? I cannot verify this, currently, but I think I never had to do this. I always executed purge only once per database, not once per instance.

  • jarmo says:


    Your install script has two issues in alter statement, wrong syntax and SYS user.

  • jarmo says:


    ORA-28154: Proxy user may not act as client ‘SYS’
    And the syntax should be ‘alter user perfstat GRANT…’

    I can see only one ‘alter user’ statement in install.sql, so don’t know what you mean another statement (install in CDB$ROOT”)?

  • Hi Jarmo,
    Oh yes, typos here 🙁
    The idea was:

    alter user perfstat grant connect through system;

    Thanks, I fixed it in Git.

  • Olivier BERNHARD says:

    Hello Franck,
    I’ve found a lot of of custom additions to vanilla statspack scripts, however i’ve never found any modification to collect SQL execution metrics by plan hash value. I may be wrong but i think SQL stats are not aggregated per plan hash value (which is obviously not included, unless i’m missing something). Are you aware of such a customization ?
    Thanks for you great work (as usual!)

  • Hi Olivier,
    No, I’m not aware of someone having done this. Probably not so easy or Oracle should have done it at that time.
    I agree it may help, but I never needed it enough to do that.

  • William Laverde says:

    Hi, In case on CDB with 3 pdbs Do I need to run 01-install.sql on every PDB or in just the CDB. It wasnt clear to me. Thanks

  • Franck Pachot says:

    Hi William,
    Statspack is not CDB aware so you install it in each PDB.
    I think it is not recommended to install it in CDB$ROOT, but I do it if I want to see what happens there.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist