Essa é uma dica rápida sobre como podemos alterar atributos de um JOB que pertence a outro usuário no Oracle, estando conectado com o usuário SYS. O “truque” se faz necessário pois uma tentativa de alterar um JOB que pertence a outro usuário, retorna o seguinte erro:

ORA-23421: job number 101 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 972
ORA-06512: at "SYS.DBMS_JOB", line 314
ORA-06512: at line 1
23421. 00000 -  "job number %s 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.

Um workaround é alterar o PRIV_USER do JOB temporiamente, deixando no nome do SYS enquanto realizamos a alteração.

Exemplo

O exemplo abaixo assume que o owner do JOB 101 é o usuário “APP”:

1) Altera o JOB para o usuário SYS:

SQL> EXEC SYS.DBMS_IJOB.CHANGE_ENV(101, NULL, 'SYS', NULL, NULL);

2) Executa a alteração desejada no JOB. Neste exemplo, estamos pinando a execução do JOB para ocorrer na instance 1 do RAC:

SQL> EXEC DBMS_JOB.INSTANCE(JOB => 101, INSTANCE => 1, FORCE => FALSE);

3) Altera o owner do JOB novamente, voltando para o nome do usuário original (APP):

SQL> EXEC SYS.DBMS_IJOB.CHANGE_ENV(101, NULL, 'APP', NULL, NULL);

4) É necessário emitir um commit explícito:

SQL> COMMIT;

Entendendo os Parâmetros da Procedure

Os parâmetros passados na procedure CHANGE_ENV representam os seguintes campos na view DBA_JOBS:

EXEC SYS.DBMS_IJOB.CHANGE_ENV(JOB, LOG_USER, PRIV_USER, SCHEMA_USER, NLS_ENV);

Um describe na package (recortado manualmente a parte da procedure CHANGE_ENV):

SQL> desc SYS.DBMS_IJOB
PROCEDURE CHANGE_ENV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN
 LUSER                          VARCHAR2                IN
 PUSER                          VARCHAR2                IN
 CUSER                          VARCHAR2                IN
 NLSENV                         VARCHAR2                IN

Note que todos os parâmetros são obrigatórios (não tem valor default), por isso passamos o valor NULL explicitamente para os demais parâmetros que não precisam ser alterados. No entanto, caso você tenha alguma outra necessidade, pode alterar qualquer um dos atributos acima. Por exemplo: alterar os atributos da coluna NLS_ENV.

Alterando o Owner do JOB Permanentemente

Talvez haja a necessidade de simplesmente alterar o owner de um JOB e mantê-lo assim, um cenário possível seria um JOB ter sido criado com o usuário SYS por engano, quando na prática ele precisaria pertencer ao schema da aplicação.

Exemplo alterando todos os atributos relacionados a usuário de um JOB:

SQL> EXEC SYS.DBMS_IJOB.CHANGE_ENV(101,'APP2','APP2','APP2',NULL);
SQL> COMMIT;

Uma versão do comando formatada para “auto entendimento” sobre os parâmetros:

BEGIN
 SYS.DBMS_IJOB.CHANGE_ENV(
  JOB    => 101,   
  LUSER  => 'APP2', -- LOG_USER
  PUSER  => 'APP2', -- PRIV_USER
  CUSER  => 'APP2', -- SCHEMA_USER
  NLSENV => NULL   -- NLS_ENV
  );
 COMMIT;
 END;
 /

Esse é uma dica válida para os JOBs criados com a package DBMS_JOB, uma vez que os JOBs criados com a DBMS_SCHEDULER podem ser alterados pelo usuário SYS normalmente. Por fim, vale salientar que o procedimento demonstrado nesse post não é oficialmente documentado pela Oracle, então talvez não seja suportado. Sempre faça por conta e risco quando achar necessário.