Skip to content

How to Fix Broken Statspack Snapshot Jobs


After we create and initiate STATSPACK in Oracle database, we sometimes might see a broken STATSPACK snapshot job stopped working for several days. I tried to run the job on the spot but it still failed. So I decide to recreate (remove + submit) the job. Here are my steps:

Login in as perfstat.

SQL> conn perfstat
Enter password:


Remove the broken job.

SQL> select job from user_jobs;


SQL> execute dbms_job.remove(183);

PL/SQL procedure successfully completed.

If you can only use SYS to remove it, you may look up the job number by this:

SQL> show user
SQL> select job from dba_jobs where what like '%statspack%';


Create a STATSPACK snapshot job by leveraging the utility spauto.sql.

SQL> @?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
Note that this job number is needed when modifying or removing
the job:


Job queue process
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
job_queue_processes                  integer                          20

Next scheduled run
The next scheduled run for this job is:

       JOB NEXT_DATE           NEXT_SEC
---------- ------------------- ------------------------
       184 2016-12-21 20:00:00 20:00:00



The snapshot job is back to normal. It will take a snapshot once an hour. If you were planning to take the snapshot every half an hour, you can change the interval like this:

SQL> execute dbms_job.interval(184,'sysdate+(1/48)');

PL/SQL procedure successfully completed.

2 thoughts on “How to Fix Broken Statspack Snapshot Jobs”

Leave a Reply

Your email address will not be published. Required fields are marked *