How to Fix Broken Statspack Snapshot Jobs

  • by

Saw a broken statspack snapshot job stopped working for several days in an Oracle 9i database. 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.

[oracle@test ~]$ sqlplus /nolog

SQL*Plus: Release - Production on Wed Dec 21 19:08:10 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn perfstat
Enter password:

Remove the broken job.

SQL> execute dbms_job.remove(183);

PL/SQL procedure successfully completed.

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.

Leave a Reply

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