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.
you could have tried to fix the job, it worked for me
EXEC DBMS_JOB.BROKEN(183,FALSE);
EXEC DBMS_JOB.RUN(183);
Thanks for your feedback!