How to Fix Broken Statspack Snapshot Jobs

Broken STATSPACK

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:
Connected.

DBMS_JOB.REMOVE

Remove the broken job.

SQL> select job from user_jobs;

       JOB
----------
       183

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
USER is "SYS"
SQL> select job from dba_jobs where what like '%statspack%';


SPAUTO

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:

     JOBNO
----------
       184


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

SQL>

DBMS_JOB.INTERVAL

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.