ORA-23421
When I tried to remove a given job from the queue, the statement failed with ORA-23421, and ORA-06512 are also in the error stack.
C:\Documents and Settings\Administrator>sqlplus / as sysdba
...
SQL> exec dbms_job.remove(83);
BEGIN dbms_job.remove(83); END;
*
ERROR at line 1:
ORA-23421: job number 83 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 680
ORA-06512: at "SYS.DBMS_JOB", line 174
ORA-06512: at line 1
Rationale
Let's see the content of ORA-23421.
Description
ORA-23421: job number string is not a job in the job queue
Cause
There is no job visible to the caller with the given job number.
Action
Choose the number of a job visible to the caller.
Oh? the job is not visible to the caller? I thought SYS can do everything he wants even if the job belongs to others.
Solution
So next, I switched to the owner of the job.
SQL> conn perfstat/perfstat
Connected.
SQL> exec dbms_job.remove(83);
PL/SQL procedure successfully completed.
ORA-23421 with ORA-06512 is solved.
If your statspack job stopped working for some reasons, you may need to fix the broken statspack snapshot job first.