[Solved] Issue: ORA-23421: job number 205759 is not a job in the job queue

Percebi que eu tinha alguns Jobs em BROKEN, e precisava normalizá-los:

SQL> SELECT JOB,SCHEMA_USER,LAST_DATE,NEXT_DATE,BROKEN FROM DBA_JOBS WHERE LOG_USER='TESTE12' AND BROKEN='Y';
 
       JOB SCHEMA_USER                    LAST_DAT NEXT_DAT B
---------- ------------------------------ -------- -------- -
    205758 TESTE12                        17/01/20 01/01/00 Y
    205759 TESTE12                        17/01/20 01/01/00 Y
    205778 TESTE12                        17/01/20 01/01/00 Y
    205739 TESTE12                        09/11/20 01/01/00 Y
    205740 TESTE12                        17/01/20 01/01/00 Y
    205780 TESTE12                        17/01/20 01/01/00 Y
    205838 TESTE12                        17/01/20 01/01/00 Y
    205886 TESTE12                        17/01/20 01/01/00 Y
    205887 TESTE12                        17/01/20 01/01/00 Y
    205883 TESTE12                        17/01/20 01/01/00 Y
    205884 TESTE12                        17/01/20 01/01/00 Y
    205885 TESTE12                        17/01/20 01/01/00 Y
 
12 linhas selecionadas.

É possível tirar o status broken do Job usando o comando abaixo:

EXEC DBMS_JOB.BROKEN(205758,FALSE);
EXEC DBMS_JOB.BROKEN(205759,FALSE);
EXEC DBMS_JOB.BROKEN(205778,FALSE);
EXEC DBMS_JOB.BROKEN(205739,FALSE);
EXEC DBMS_JOB.BROKEN(205740,FALSE);
EXEC DBMS_JOB.BROKEN(205780,FALSE);
EXEC DBMS_JOB.BROKEN(205838,FALSE);
EXEC DBMS_JOB.BROKEN(205886,FALSE);
EXEC DBMS_JOB.BROKEN(205887,FALSE);
EXEC DBMS_JOB.BROKEN(205883,FALSE);
EXEC DBMS_JOB.BROKEN(205884,FALSE);
EXEC DBMS_JOB.BROKEN(205885,FALSE);

Porém, ao tentar rodar o primeiro deles, me deparei com este erro (estava logado com um usuário DBA):

SQL> EXEC DBMS_JOB.BROKEN(205758,TRUE);
BEGIN DBMS_JOB.BROKEN(205758,TRUE); END;
 
*
ERRO na linha 1:
ORA-23421: job number 205758 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 592
ORA-06512: at "SYS.DBMS_JOB", line 252
ORA-06512: at line 1

Logo, percebi que mesmo conectado com um usuário DBA, precisaria me conectar com o owner dono do Job (e não adiantar tentar fazer o ALTER SESSION SET CURRENT_SCHEMA). Porém, eu não tinha a senha deste owner, e precisei salvar o seu HASH, alterar momentaneamente a sua senha, conforme abaixo:

SQL> SELECT NAME,PASSWORD FROM SYS.USER$ WHERE NAME='TESTE12';
 
NAME                           PASSWORD
------------------------------ ------------------------------
TESTE12                        D604551CB7C030FF
SQL> ALTER USER TESTE12 IDENTIFIED BY TESTE12 ACCOUNT UNLOCK;
 
Usußrio alterado.

Logando com o owner e executando os scripts:

C:\Users\bruno>sqlplus TESTE12/TESTE12@BANCO
 
SQL*Plus: Release 12.2.0.1.0 Production on Seg Jan 18 09:46:27 2021
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Horßrio do ·ltimo log-in bem-sucedido: Qui Ago 13 2020 17:13:14 -03:00
 
Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
 
SQL> EXEC DBMS_JOB.BROKEN(205758,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205759,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205778,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205739,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205740,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205780,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205838,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205886,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205887,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205883,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205884,FALSE);
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_JOB.BROKEN(205885,FALSE);
 
PL/SQL procedure successfully completed.

Voltando a senha antiga do owner:

SQL> ALTER USER TESTE12 IDENTIFIED BY VALUES 'D604551CB7C030FF' ACCOUNT UNLOCK;
 
Usußrio alterado.

Ou, se você puder, é possível rodar o Job manualmente, conforme exemplo abaixo:

EXEC DBMS_JOB.RUN(205758);

Leave a Comment Cancel Reply