[ORCL.oracle@furushimait_labs ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 10 14:52:51 2025
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> @job_admin_setup_completo.sql

═════════════════════════════════════════════════════════════════════════════════════════════
Iniciando job_admin_setup_completo.sql
═════════════════════════════════════════════════════════════════════════════════════════════
Owner atual: SYS

PL/SQL procedure successfully completed.


── 1) Criando/Atualizando Views Consolidadas …

View created.


View created.


View created.


View created.

✓ Views criadas/atualizadas com sucesso.

── 2) Criando/Atualizando Pacote (SPEC) …

Package created.

No errors.

── 3) Criando/Atualizando Pacote (BODY) …

Package body created.

No errors.
✓ Pacote criado/atualizado com sucesso.

── 4) Segurança: ROLE, Grants e Sinônimos Públicos …
Role JOBDBMSSCHEDULERALL já existente.

PL/SQL procedure successfully completed.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

Sinônimos públicos criados/atualizados.

PL/SQL procedure successfully completed.

✓ Segurança configurada (ROLE + grants + sinônimos públicos).

── 5) Auto-Validação (não intrusiva) …
Pacote PKG_JOB_ADMIN sem erros de compilação.

PL/SQL procedure successfully completed.

Views acessíveis: JOB_ADMIN_ALL_JOBS e JOB_ADMIN_RUNNING OK.

PL/SQL procedure successfully completed.


── 6) Exemplos de Uso para referência  …

-- Inventário:
BEGIN
pkg_job_admin.list_jobs;                          -- todos os jobs
pkg_job_admin.list_jobs('DEMOAPP');               -- por owner
pkg_job_admin.list_jobs(p_like => 'JOB_%');       -- por padrão (LIKE)
END
/
-- Em execução agora:
BEGIN
pkg_job_admin.list_running_jobs;                       -- tudo
pkg_job_admin.list_running_jobs('DEMOAPP');            -- por owner
pkg_job_admin.list_running_jobs(p_like => 'SYNC_%');   -- por padrão (LIKE)
END
/
-- Operações pontuais:
-- SCHEDULER (por nome; owner opcional se único no banco)
-- BEGIN
--   pkg_job_admin.disable_job('DEMOAPP.JOB_ATUALIZA_INDICES', p_stop_running => TRUE)
--   pkg_job_admin.enable_job ('DEMOAPP.JOB_ATUALIZA_INDICES')
-- END
-- /
-- DBMS_JOB (por número; owner apenas para validar pertinência quando desejar)
-- BEGIN
--   pkg_job_admin.disable_job('128', p_owner => 'SYS', p_stop_running => TRUE)
--   pkg_job_admin.enable_job ('128', p_owner => 'SYS')
-- END
-- /
-- Lote por padrão (por tipo):
-- BEGIN
--   pkg_job_admin.set_jobs_by_pattern(
--     p_name_like    => 'JOB_SYNC_%',
--     p_enable       => FALSE,
--     p_owner        => 'DEMOAPP',
--     p_type         => pkg_job_admin.c_type_scheduler, -- SCHEDULER | DBMS_JOB | ANY
--     p_stop_running => TRUE
--   )
-- END
-- /
-- Visões consolidadas:
-- SELECT * FROM job_admin_all_jobs_v ORDER BY owner, job_type, job_name
-- SELECT * FROM job_admin_running_v  ORDER BY running_instance, job_type, job_name

═════════════════════════════════════════════════════════════════════════════════════════════
Conclusão: job_admin_setup_completo.sql executado.
Para conceder a ROLE a um usuário final, utilize:
GRANT JOBDBMSSCHEDULERALL TO nome_do_usuario
═════════════════════════════════════════════════════════════════════════════════════════════

-- =================================================================================================
--  job_admin_setup_completo.sql
--  Administração unificada de jobs no Oracle Database (DBMS_SCHEDULER e DBMS_JOB)
--  Autor: Carlos Furushima — Furushima IT
--  Direitos Autorais: Este script é propriedade intelectual de Furushima IT. Todos os direitos reservados.
--
--  OBJETIVO
--  --------
--  Fornecer uma interface única, consistente e profissional para:
--    • Inventariar jobs do SCHEDULER e do DBMS_JOB em visões consolidadas;
--    • Consultar jobs em execução (tempo decorrido e sessão, quando aplicável);
--    • Habilitar/Desabilitar jobs individualmente (por nome ou número);
--    • Habilitar/Desabilitar em lote por padrão de nome;
--    • Padronizar o consumo por meio de uma ROLE e sinônimos públicos.
--
--  O QUE É CRIADO
--  --------------
--    Views:
--      - JOB_ADMIN_ALL_JOBS      : inventário unificado (SCHEDULER + DBMS_JOB)
--      - JOB_ADMIN_RUNNING       : execução unificada (SCHEDULER + DBMS_JOB)
--      - JOB_ADMIN_ALL_JOBS_V    : inventário formatado para leitura humana
--      - JOB_ADMIN_RUNNING_V     : execução formatada para leitura humana
--
--    Pacote:
--      - PKG_JOB_ADMIN           : utilitários de listagem e (des)ativação unificada
--
--    Segurança e Acesso:
--      - ROLE JOBDBMSSCHEDULERALL com grants de SELECT/EXECUTE
--      - Sinônimos públicos para facilitar o uso por quem tiver a ROLE
--
--  PRÉ-REQUISITOS
--  --------------
--  • Executar com um usuário com privilégios para consultar dicionários DBA_* e GV$* e criar objetos.
--  • O pacote é AUTHID DEFINER (permissões do dono do pacote). Usuários finais só precisam da ROLE.
--
--  USO TÍPICO
--  ----------
--  -- Inventário:
--  BEGIN
--    pkg_job_admin.list_jobs;                       -- tudo
--    pkg_job_admin.list_jobs('DEMOAPP');            -- por owner
--    pkg_job_admin.list_jobs(p_like => 'JOB_%');    -- por padrão (LIKE)
--  END;
--  /
--
--  -- Executando agora:
--  BEGIN
--    pkg_job_admin.list_running_jobs;                       -- tudo
--    pkg_job_admin.list_running_jobs('DEMOAPP');            -- por owner
--    pkg_job_admin.list_running_jobs(p_like => 'SYNC_%');   -- por padrão (LIKE)
--  END;
--  /
--
--  -- Operações pontuais:
--  -- SCHEDULER (identificador por nome, owner opcional se único no banco)
--  -- BEGIN
--  --   pkg_job_admin.disable_job('DEMOAPP.JOB_ATUALIZA_INDICES', p_stop_running => TRUE);
--  --   pkg_job_admin.enable_job ('DEMOAPP.JOB_ATUALIZA_INDICES');
--  -- END;
--  -- /
--  --
--  -- DBMS_JOB (identificador numérico; owner opcional apenas para validar pertinência)
--  -- BEGIN
--  --   pkg_job_admin.disable_job('128', p_owner => 'SYS', p_stop_running => TRUE);
--  --   pkg_job_admin.enable_job ('128', p_owner => 'SYS');
--  -- END;
--  -- /
--
--  -- Lote por padrão (por tipo):
--  -- BEGIN
--  --   pkg_job_admin.set_jobs_by_pattern(
--  --     p_name_like    => 'JOB_SYNC_%',
--  --     p_enable       => FALSE,                               -- desativar
--  --     p_owner        => 'DEMOAPP',                           -- opcional
--  --     p_type         => pkg_job_admin.c_type_scheduler,      -- SCHEDULER | DBMS_JOB | ANY
--  --     p_stop_running => TRUE
--  --   );
--  -- END;
--  -- /
--
--  DICAS
--  -----
--  • O script é idempotente (CREATE OR REPLACE; checagens seguras). Pode ser executado mais de uma vez.
--  • Saída “limpa”: mensagens organizadas por PROMPT/DBMS_OUTPUT para auditoria e documentação.
-- =================================================================================================

SET FEEDBACK ON
SET SERVEROUTPUT ON SIZE UNLIMITED
SET VERIFY OFF

PROMPT
PROMPT ═════════════════════════════════════════════════════════════════════════════════════════════
PROMPT   Iniciando job_admin_setup_completo.sql
PROMPT ═════════════════════════════════════════════════════════════════════════════════════════════

-- -----------------------------------------------------------------------------------------------
-- 0) CONTEXTO — Mostra o usuário (owner) sob o qual os objetos serão criados
-- -----------------------------------------------------------------------------------------------
DECLARE
  l_user VARCHAR2(128) := USER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Owner atual: '||l_user);
END;
/

PROMPT
PROMPT ── 1) Criando/Atualizando Views Consolidadas …

-- ===============================================================================================
-- 1.1) JOB_ADMIN_ALL_JOBS
--      Consolida em uma única visão os jobs do DBMS_SCHEDULER e do DBMS_JOB.
--      Observações de tipos:
--        • next_run_date / last_start_date: TIMESTAMP WITH TIME ZONE (SCHEDULER) x DATE (DBMS_JOB)
--          -> o Oracle promove DATE para TIMESTAMP durante o UNION ALL (sem perda de funcionalidade).
--        • last_run_duration: INTERVAL no SCHEDULER; CAST de NULL para INTERVAL no DBMS_JOB.
--        • job_action / repeat_interval: normalizados para VARCHAR2 (até 4000) no DBMS_JOB.
-- ===============================================================================================
CREATE OR REPLACE VIEW job_admin_all_jobs AS
    SELECT owner,
           job_name,
           'SCHEDULER'               AS job_type,
           CASE WHEN enabled = 'TRUE' THEN 'TRUE' ELSE 'FALSE' END AS enabled,
           state,
           next_run_date,                                -- TIMESTAMP WITH TIME ZONE
           last_start_date,                              -- TIMESTAMP WITH TIME ZONE
           last_run_duration,                            -- INTERVAL DAY TO SECOND
           job_action,                                   -- VARCHAR2/CLOB (na maioria dos casos, até 4000)
           repeat_interval                               -- VARCHAR2
      FROM dba_scheduler_jobs
  UNION ALL
    SELECT schema_user                                   AS owner,
           TO_CHAR(job)                                  AS job_name,
           'DBMS_JOB'                                    AS job_type,
           CASE broken WHEN 'Y' THEN 'FALSE' ELSE 'TRUE' END AS enabled,
           CAST(NULL AS VARCHAR2(30))                    AS state,
           next_date,                                    -- DATE (promovido a TIMESTAMP no UNION)
           last_date                                     AS last_start_date, -- DATE
           CAST(NULL AS INTERVAL DAY TO SECOND)          AS last_run_duration,
           SUBSTR(what,     4000)                        AS job_action,      -- LONG -> VARCHAR2
           SUBSTR(interval, 4000)                        AS repeat_interval  -- LONG -> VARCHAR2
      FROM dba_jobs
;

-- ===============================================================================================
-- 1.2) JOB_ADMIN_RUNNING
--      Consolida os jobs em execução. Para DBMS_JOB, calcula elapsed pelo logon_time da sessão.
--      Observações:
--        • cpu_used não se aplica ao DBMS_JOB; representa-se com NULL (cast para INTERVAL).
-- ===============================================================================================
CREATE OR REPLACE VIEW job_admin_running AS
    SELECT owner,
           job_name,
           'SCHEDULER'               AS job_type,
           session_id,
           running_instance,
           elapsed_time,                                 -- INTERVAL
           cpu_used                                      -- INTERVAL
      FROM dba_scheduler_running_jobs
  UNION ALL
    SELECT j.schema_user           AS owner,
           TO_CHAR(r.job)          AS job_name,
           'DBMS_JOB'              AS job_type,
           r.sid                   AS session_id,
           s.inst_id               AS running_instance,
           NUMTODSINTERVAL(TRUNC((SYSDATE - s.logon_time)*86400),'SECOND') AS elapsed_time,
           CAST(NULL AS INTERVAL DAY TO SECOND)          AS cpu_used
      FROM dba_jobs_running r
      JOIN gv$session s ON s.sid = r.sid
      JOIN dba_jobs j   ON j.job = r.job
;

-- ===============================================================================================
-- 1.3) JOB_ADMIN_ALL_JOBS_V — Versão “amigável” (formatos para leitura humana)
-- ===============================================================================================
CREATE OR REPLACE VIEW job_admin_all_jobs_v AS
SELECT owner, job_name, job_type, enabled, state,
       TO_CHAR(next_run_date,'YYYY-MM-DD HH24:MI:SS')      AS next_run_date,
       TO_CHAR(last_start_date,'YYYY-MM-DD HH24:MI:SS')    AS last_start_date,
       CASE WHEN last_run_duration IS NULL THEN '-' ELSE
         EXTRACT(DAY FROM last_run_duration)||' '||
         LPAD(EXTRACT(HOUR FROM last_run_duration),2,'0')||':'||
         LPAD(EXTRACT(MINUTE FROM last_run_duration),2,'0')||':'||
         LPAD(TRUNC(EXTRACT(SECOND FROM last_run_duration)),2,'0')
       END AS last_run_duration,
       job_action, repeat_interval
  FROM job_admin_all_jobs
;

-- ===============================================================================================
-- 1.4) JOB_ADMIN_RUNNING_V — Versão “amigável” (formatos para leitura humana)
-- ===============================================================================================
CREATE OR REPLACE VIEW job_admin_running_v AS
SELECT owner, job_name, job_type, session_id, running_instance,
       CASE WHEN elapsed_time IS NULL THEN '-' ELSE
         EXTRACT(DAY FROM elapsed_time)||' '||
         LPAD(EXTRACT(HOUR FROM elapsed_time),2,'0')||':'||
         LPAD(EXTRACT(MINUTE FROM elapsed_time),2,'0')||':'||
         LPAD(TRUNC(EXTRACT(SECOND FROM elapsed_time)),2,'0')
       END AS elapsed_time,
       CASE WHEN cpu_used IS NULL THEN '-' ELSE
         EXTRACT(DAY FROM cpu_used)||' '||
         LPAD(EXTRACT(HOUR FROM cpu_used),2,'0')||':'||
         LPAD(EXTRACT(MINUTE FROM cpu_used),2,'0')||':'||
         LPAD(TRUNC(EXTRACT(SECOND FROM cpu_used)),2,'0')
       END AS cpu_used
  FROM job_admin_running
;

PROMPT ✓ Views criadas/atualizadas com sucesso.

PROMPT
PROMPT ── 2) Criando/Atualizando Pacote (SPEC) …

-- =================================================================================================
-- 2) ESPECIFICAÇÃO DO PACOTE — PKG_JOB_ADMIN
--    Contém constantes de tipo e procedimentos públicos de listagem e (des)ativação unificada.
-- =================================================================================================
CREATE OR REPLACE PACKAGE pkg_job_admin AUTHID DEFINER AS
  -- Constantes para filtro por tipo
  c_type_scheduler CONSTANT VARCHAR2(11) := 'SCHEDULER';
  c_type_dbms_job  CONSTANT VARCHAR2(8)  := 'DBMS_JOB';
  c_type_any       CONSTANT VARCHAR2(3)  := 'ANY';

  -- Listagem de jobs (inventário)
  PROCEDURE list_jobs(
    p_owner IN VARCHAR2 DEFAULT NULL,   -- filtra por owner
    p_like  IN VARCHAR2 DEFAULT NULL    -- filtra por padrão (LIKE)
  );

  -- Listagem de jobs em execução
  PROCEDURE list_running_jobs(
    p_owner IN VARCHAR2 DEFAULT NULL,   -- filtra por owner
    p_like  IN VARCHAR2 DEFAULT NULL    -- filtra por padrão (LIKE)
  );

  -- Habilita/Desabilita um job (identificador por nome SCHEDULER ou número DBMS_JOB)
  PROCEDURE set_job_enabled(
    p_job_identifier IN VARCHAR2,       -- 'OWNER.JOB_NAME' | 'JOB_NAME' | '123' (DBMS_JOB)
    p_enable         IN BOOLEAN,        -- TRUE = ENABLE; FALSE = DISABLE
    p_owner          IN VARCHAR2 DEFAULT NULL,  -- para SCHEDULER sem owner único ou DBMS_JOB para validar owner
    p_stop_running   IN BOOLEAN DEFAULT FALSE   -- encerra execução corrente (se houver)
  );

  -- Açúcares sintáticos (conveniências)
  PROCEDURE enable_job(
    p_job_identifier IN VARCHAR2,
    p_owner          IN VARCHAR2 DEFAULT NULL,
    p_stop_running   IN BOOLEAN DEFAULT FALSE
  );

  PROCEDURE disable_job(
    p_job_identifier IN VARCHAR2,
    p_owner          IN VARCHAR2 DEFAULT NULL,
    p_stop_running   IN BOOLEAN DEFAULT TRUE
  );

  -- Habilita/Desabilita em lote por padrão de nome e tipo
  PROCEDURE set_jobs_by_pattern(
    p_name_like     IN VARCHAR2,                      -- ex: 'JOB_SYNC_%'
    p_enable        IN BOOLEAN,                       -- TRUE/FALSE
    p_owner         IN VARCHAR2 DEFAULT NULL,         -- opcional
    p_type          IN VARCHAR2 DEFAULT c_type_any,   -- SCHEDULER | DBMS_JOB | ANY
    p_stop_running  IN BOOLEAN DEFAULT FALSE
  );
END pkg_job_admin;
/
SHOW ERRORS

PROMPT
PROMPT ── 3) Criando/Atualizando Pacote (BODY) …

-- =================================================================================================
-- 3) CORPO DO PACOTE — PKG_JOB_ADMIN
--    Implementa utilitários de formatação, detecção de owner/instância, e operações de controle.
-- =================================================================================================
CREATE OR REPLACE PACKAGE BODY pkg_job_admin AS

  -- ---------------------------------------------------------------------------------------------
  -- Função auxiliar: formata INTERVAL DAY TO SECOND em 'DD HH24:MI:SS'
  -- ---------------------------------------------------------------------------------------------
  FUNCTION fmt_ds(p INTERVAL DAY TO SECOND) RETURN VARCHAR2 IS
    l_day  PLS_INTEGER; l_hour PLS_INTEGER; l_min PLS_INTEGER; l_sec PLS_INTEGER;
  BEGIN
    IF p IS NULL THEN RETURN '-'; END IF;
    l_day  := EXTRACT(DAY    FROM p);
    l_hour := EXTRACT(HOUR   FROM p);
    l_min  := EXTRACT(MINUTE FROM p);
    l_sec  := TRUNC(EXTRACT(SECOND FROM p));
    RETURN LPAD(l_day,2,'0')||' '||LPAD(l_hour,2,'0')||':'||LPAD(l_min,2,'0')||':'||LPAD(l_sec,2,'0');
  END fmt_ds;

  -- ---------------------------------------------------------------------------------------------
  -- Função auxiliar: TO_CHAR seguro para datas que podem ser NULL (retorna '-' no NULL)
  -- ---------------------------------------------------------------------------------------------
  FUNCTION to_char_nvl(p_date DATE) RETURN VARCHAR2 IS
  BEGIN
    RETURN CASE WHEN p_date IS NULL THEN '-' ELSE TO_CHAR(p_date,'YYYY-MM-DD HH24:MI:SS') END;
  END;

  -- ---------------------------------------------------------------------------------------------
  -- Monta o nome totalmente qualificado do job de SCHEDULER
  -- ---------------------------------------------------------------------------------------------
  FUNCTION sched_job_fqname(p_owner IN VARCHAR2, p_job_name IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN CASE WHEN p_owner IS NOT NULL THEN UPPER(p_owner)||'.'||UPPER(p_job_name) ELSE UPPER(p_job_name) END;
  END;

  -- ---------------------------------------------------------------------------------------------
  -- Solicita parada de job SCHEDULER, se estiver em execução (silencioso em falhas)
  -- ---------------------------------------------------------------------------------------------
  PROCEDURE stop_scheduler_if_running(p_owner IN VARCHAR2, p_job_name IN VARCHAR2) IS
    l_fq VARCHAR2(261) := sched_job_fqname(p_owner, p_job_name);
  BEGIN
    DBMS_SCHEDULER.STOP_JOB(job_name => l_fq, force => TRUE);
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;

  -- ---------------------------------------------------------------------------------------------
  -- Encerra sessão de DBMS_JOB, se houver (suporte RAC com @inst_id)
  -- ---------------------------------------------------------------------------------------------
  PROCEDURE kill_dbms_job_session(p_job_id IN NUMBER) IS
    l_sid     NUMBER;
    l_serial  NUMBER;
    l_inst    NUMBER;
    l_stmt    VARCHAR2(200);
  BEGIN
    SELECT s.sid, s.serial#, s.inst_id
      INTO l_sid, l_serial, l_inst
      FROM dba_jobs_running r
      JOIN gv$session s ON s.sid = r.sid
     WHERE r.job = p_job_id
       AND ROWNUM = 1;

    l_stmt := 'ALTER SYSTEM KILL SESSION '''||l_sid||','||l_serial||
              CASE WHEN l_inst IS NOT NULL THEN ',@'||l_inst ELSE NULL END||''' IMMEDIATE';
    EXECUTE IMMEDIATE l_stmt;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
    WHEN OTHERS THEN NULL;
  END;

  -- ---------------------------------------------------------------------------------------------
  -- Resolve owner de job SCHEDULER quando usuário não informou p_owner
  -- Lança erro se não encontrado ou se existir em múltiplos owners.
  -- ---------------------------------------------------------------------------------------------
  PROCEDURE resolve_scheduler(p_owner IN VARCHAR2, p_job_name IN VARCHAR2,
                              o_owner OUT VARCHAR2, o_job_name OUT VARCHAR2) IS
    l_cnt INTEGER;
  BEGIN
    o_job_name := UPPER(p_job_name);
    IF p_owner IS NOT NULL THEN o_owner := UPPER(p_owner); RETURN; END IF;

    SELECT COUNT(DISTINCT owner) INTO l_cnt
      FROM dba_scheduler_jobs
     WHERE job_name = o_job_name;

    IF l_cnt = 0 THEN
      RAISE NO_DATA_FOUND;
    ELSIF l_cnt > 1 THEN
      RAISE_APPLICATION_ERROR(-20002,
        'JOB_NAME existe em múltiplos owners. Informe p_owner. JOB_NAME='||o_job_name);
    ELSE
      SELECT owner INTO o_owner
        FROM dba_scheduler_jobs
       WHERE job_name = o_job_name
         AND ROWNUM = 1;
    END IF;
  END resolve_scheduler;

  -- ---------------------------------------------------------------------------------------------
  -- Listagem: inventário unificado
  -- ---------------------------------------------------------------------------------------------
  PROCEDURE list_jobs(p_owner IN VARCHAR2 DEFAULT NULL, p_like IN VARCHAR2 DEFAULT NULL) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('=== JOBS (SCHEDULER) ===');
    FOR r IN (
      SELECT owner, job_name, enabled, state, next_run_date, job_action
        FROM dba_scheduler_jobs
       WHERE (p_owner IS NULL OR owner = UPPER(p_owner))
         AND (p_like  IS NULL OR job_name LIKE UPPER(p_like))
       ORDER BY owner, job_name
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(r.owner||'.'||r.job_name||
                           ' | enabled='||r.enabled||
                           ' | state='||NVL(r.state,'-')||
                           ' | next='||to_char_nvl(CAST(r.next_run_date AS DATE))||
                           ' | action='||SUBSTR(r.job_action,1,60));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(10)||'=== JOBS (DBMS_JOB) ===');
    FOR r IN (
      SELECT schema_user AS owner, job AS job_id, DECODE(broken,'Y','FALSE','TRUE') AS enabled,
             next_date, what
        FROM dba_jobs
       WHERE (p_owner IS NULL OR schema_user = UPPER(p_owner))
         AND (p_like  IS NULL OR TO_CHAR(job) LIKE p_like OR UPPER(what) LIKE UPPER(p_like))
       ORDER BY schema_user, job
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(r.owner||'.'||r.job_id||
                           ' | enabled='||r.enabled||
                           ' | next='||to_char_nvl(r.next_date)||
                           ' | what='||SUBSTR(r.what,1,60));
    END LOOP;
  END list_jobs;

  -- ---------------------------------------------------------------------------------------------
  -- Listagem: execução unificada
  -- ---------------------------------------------------------------------------------------------
  PROCEDURE list_running_jobs(p_owner IN VARCHAR2 DEFAULT NULL, p_like IN VARCHAR2 DEFAULT NULL) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('=== RUNNING (SCHEDULER) ===');
    FOR r IN (
      SELECT owner, job_name, session_id, running_instance, elapsed_time, cpu_used
        FROM dba_scheduler_running_jobs
       WHERE (p_owner IS NULL OR owner = UPPER(p_owner))
         AND (p_like  IS NULL OR job_name LIKE UPPER(p_like))
       ORDER BY running_instance, job_name
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(r.owner||'.'||r.job_name||
                           ' | sid='||r.session_id||
                           ' | inst='||r.running_instance||
                           ' | elapsed='||fmt_ds(r.elapsed_time)||
                           ' | cpu='||fmt_ds(r.cpu_used));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(10)||'=== RUNNING (DBMS_JOB) ===');
    FOR r IN (
      SELECT j.schema_user AS owner, r.job AS job_id, s.sid, s.serial#,
             s.inst_id AS running_instance,
             NUMTODSINTERVAL(TRUNC((SYSDATE - s.logon_time)*86400),'SECOND') AS elapsed_time,
             j.what
        FROM dba_jobs_running r
        JOIN gv$session s ON s.sid = r.sid
        JOIN dba_jobs j   ON j.job = r.job
       WHERE (p_owner IS NULL OR j.schema_user = UPPER(p_owner))
         AND (p_like  IS NULL OR TO_CHAR(r.job) LIKE p_like OR UPPER(j.what) LIKE UPPER(p_like))
       ORDER BY running_instance, job_id
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(r.owner||'.'||r.job_id||
                           ' | sid='||r.sid||','||r.serial#||
                           ' | inst='||r.running_instance||
                           ' | elapsed='||fmt_ds(r.elapsed_time));
    END LOOP;
  END list_running_jobs;

  -- ---------------------------------------------------------------------------------------------
  -- Núcleo: habilita/desabilita por identificador (nome SCHEDULER ou número DBMS_JOB)
  -- ---------------------------------------------------------------------------------------------
  PROCEDURE set_job_enabled(
    p_job_identifier IN VARCHAR2,
    p_enable         IN BOOLEAN,
    p_owner          IN VARCHAR2 DEFAULT NULL,
    p_stop_running   IN BOOLEAN DEFAULT FALSE
  ) IS
    l_owner    VARCHAR2(128);
    l_job_name VARCHAR2(128);
    l_job_id   NUMBER;
  BEGIN
    -- 1) Tentar SCHEDULER (nome)
    BEGIN
      IF REGEXP_LIKE(p_job_identifier,'^\d+$') THEN RAISE NO_DATA_FOUND; END IF;

      IF INSTR(p_job_identifier,'.') > 0 THEN
        l_owner    := UPPER(REGEXP_SUBSTR(p_job_identifier,'^([^\.]+)'));
        l_job_name := UPPER(REGEXP_SUBSTR(p_job_identifier,'\.([^\.]+)$',1,1,NULL,1));
      ELSE
        l_job_name := UPPER(p_job_identifier);
        resolve_scheduler(p_owner, l_job_name, l_owner, l_job_name);
      END IF;

      -- valida existência
      DECLARE l_dummy PLS_INTEGER;
      BEGIN
        SELECT 1 INTO l_dummy
          FROM dba_scheduler_jobs
         WHERE owner = l_owner AND job_name = l_job_name AND ROWNUM = 1;
      END;

      IF p_stop_running THEN
        stop_scheduler_if_running(l_owner, l_job_name);
      END IF;

      IF p_enable THEN
        DBMS_SCHEDULER.ENABLE( sched_job_fqname(l_owner, l_job_name) );
      ELSE
        DBMS_SCHEDULER.DISABLE( sched_job_fqname(l_owner, l_job_name) );
      END IF;

      DBMS_OUTPUT.PUT_LINE('OK (SCHEDULER) '||
        sched_job_fqname(l_owner,l_job_name)||' => '||
        CASE WHEN p_enable THEN 'ENABLED' ELSE 'DISABLED' END);

      RETURN;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL; -- não era scheduler; tentar DBMS_JOB
    END;

    -- 2) Tentar DBMS_JOB (número)
    BEGIN
      l_job_id := TO_NUMBER(TRIM(p_job_identifier));

      -- valida existência (restringe owner se informado)
      DECLARE l_dummy2 PLS_INTEGER;
      BEGIN
        SELECT 1 INTO l_dummy2
          FROM dba_jobs
         WHERE job = l_job_id
           AND (p_owner IS NULL OR schema_user = UPPER(p_owner))
           AND ROWNUM = 1;
      END;

      IF p_stop_running THEN
        kill_dbms_job_session(l_job_id);
      END IF;

      DBMS_JOB.BROKEN(l_job_id, NOT p_enable);

      DBMS_OUTPUT.PUT_LINE('OK (DBMS_JOB) '||l_job_id||' => '||
        CASE WHEN p_enable THEN 'ENABLED' ELSE 'DISABLED' END);

      RETURN;
    EXCEPTION
      WHEN VALUE_ERROR    THEN NULL;
      WHEN INVALID_NUMBER THEN NULL;
      WHEN NO_DATA_FOUND  THEN NULL;
    END;

    -- 3) Não encontrado em nenhuma modalidade
    RAISE_APPLICATION_ERROR(-20001,
      'Job não encontrado como SCHEDULER (por nome) nem DBMS_JOB (por número): '||p_job_identifier);
  END set_job_enabled;

  -- Açúcares sintáticos
  PROCEDURE enable_job(
    p_job_identifier IN VARCHAR2,
    p_owner          IN VARCHAR2 DEFAULT NULL,
    p_stop_running   IN BOOLEAN  DEFAULT FALSE
  ) IS
  BEGIN
    set_job_enabled(p_job_identifier => p_job_identifier,
                    p_enable         => TRUE,
                    p_owner          => p_owner,
                    p_stop_running   => p_stop_running);
  END;

  PROCEDURE disable_job(
    p_job_identifier IN VARCHAR2,
    p_owner          IN VARCHAR2 DEFAULT NULL,
    p_stop_running   IN BOOLEAN  DEFAULT TRUE
  ) IS
  BEGIN
    set_job_enabled(p_job_identifier => p_job_identifier,
                    p_enable         => FALSE,
                    p_owner          => p_owner,
                    p_stop_running   => p_stop_running);
  END;

  -- ---------------------------------------------------------------------------------------------
  -- Habilita/Desabilita em lote por padrão (LIKE) e, opcionalmente, por tipo
  -- ---------------------------------------------------------------------------------------------
  PROCEDURE set_jobs_by_pattern(
    p_name_like     IN VARCHAR2,
    p_enable        IN BOOLEAN,
    p_owner         IN VARCHAR2 DEFAULT NULL,
    p_type          IN VARCHAR2 DEFAULT c_type_any,
    p_stop_running  IN BOOLEAN  DEFAULT FALSE
  ) IS
  BEGIN
    -- SCHEDULER
    IF UPPER(p_type) IN (c_type_any, c_type_scheduler) THEN
      FOR r IN (
        SELECT owner, job_name
          FROM dba_scheduler_jobs
         WHERE (p_owner IS NULL OR owner = UPPER(p_owner))
           AND job_name LIKE UPPER(p_name_like)
      ) LOOP
        set_job_enabled(r.owner||'.'||r.job_name, p_enable, NULL, p_stop_running);
      END LOOP;
    END IF;

    -- DBMS_JOB
    IF UPPER(p_type) IN (c_type_any, c_type_dbms_job) THEN
      FOR r IN (
        SELECT schema_user AS owner, job
          FROM dba_jobs
         WHERE (p_owner IS NULL OR schema_user = UPPER(p_owner))
           AND (TO_CHAR(job) LIKE p_name_like OR UPPER(what) LIKE UPPER(p_name_like))
      ) LOOP
        set_job_enabled(TO_CHAR(r.job), p_enable, r.owner, p_stop_running);
      END LOOP;
    END IF;
  END set_jobs_by_pattern;

END pkg_job_admin;
/
SHOW ERRORS

PROMPT ✓ Pacote criado/atualizado com sucesso.

PROMPT
PROMPT ── 4) Segurança: ROLE, Grants e Sinônimos Públicos …

-- =================================================================================================
-- 4.1) ROLE — criada apenas se não existir
-- =================================================================================================
DECLARE
  l_cnt INTEGER;
BEGIN
  SELECT COUNT(*) INTO l_cnt FROM dba_roles WHERE role = 'JOBDBMSSCHEDULERALL';
  IF l_cnt = 0 THEN
    EXECUTE IMMEDIATE 'CREATE ROLE JOBDBMSSCHEDULERALL';
    DBMS_OUTPUT.PUT_LINE('Role JOBDBMSSCHEDULERALL criada.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Role JOBDBMSSCHEDULERALL já existente.');
  END IF;
END;
/

-- =================================================================================================
-- 4.2) GRANTS — executar o pacote e consultar as views por meio da ROLE
-- =================================================================================================
GRANT EXECUTE ON pkg_job_admin        TO JOBDBMSSCHEDULERALL;
GRANT SELECT  ON job_admin_all_jobs   TO JOBDBMSSCHEDULERALL;
GRANT SELECT  ON job_admin_running    TO JOBDBMSSCHEDULERALL;
GRANT SELECT  ON job_admin_all_jobs_v TO JOBDBMSSCHEDULERALL;
GRANT SELECT  ON job_admin_running_v  TO JOBDBMSSCHEDULERALL;

-- =================================================================================================
-- 4.3) SINÔNIMOS PÚBLICOS — apontam para os objetos do OWNER atual
--     Observação: garantem uso uniforme via “public synonyms” para quem tiver a ROLE.
-- =================================================================================================
DECLARE
  l_owner VARCHAR2(128) := USER;
BEGIN
  EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM job_admin_all_jobs   FOR '||l_owner||'.job_admin_all_jobs';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM job_admin_running    FOR '||l_owner||'.job_admin_running';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM job_admin_all_jobs_v FOR '||l_owner||'.job_admin_all_jobs_v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM job_admin_running_v  FOR '||l_owner||'.job_admin_running_v';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM pkg_job_admin        FOR '||l_owner||'.pkg_job_admin';
  DBMS_OUTPUT.PUT_LINE('Sinônimos públicos criados/atualizados.');
END;
/

PROMPT ✓ Segurança configurada (ROLE + grants + sinônimos públicos).

PROMPT
PROMPT ── 5) Auto-Validação (não intrusiva) …

-- =================================================================================================
-- 5.1) Verifica erros de compilação do pacote
-- =================================================================================================
DECLARE
  l_errs INTEGER;
BEGIN
  SELECT COUNT(*) INTO l_errs
    FROM user_errors
   WHERE name = 'PKG_JOB_ADMIN';

  IF l_errs = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Pacote PKG_JOB_ADMIN sem erros de compilação.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('ATENÇÃO: Encontrado(s) '||l_errs||' erro(s) em PKG_JOB_ADMIN:');
    FOR r IN (
      SELECT type, name, line, position, SUBSTR(text,1,200) AS text
        FROM user_errors
       WHERE name = 'PKG_JOB_ADMIN'
       ORDER BY sequence
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(r.type||' '||r.name||' @'||r.line||':'||r.position||' - '||r.text);
    END LOOP;
  END IF;
END;
/

-- =================================================================================================
-- 5.2) Validação simples de acesso às views (sanidade)
-- =================================================================================================
DECLARE
  l_all NUMBER; l_run NUMBER;
BEGIN
  SELECT COUNT(*) INTO l_all FROM job_admin_all_jobs WHERE ROWNUM <= 1;
  SELECT COUNT(*) INTO l_run FROM job_admin_running  WHERE ROWNUM <= 1;
  DBMS_OUTPUT.PUT_LINE('Views acessíveis: JOB_ADMIN_ALL_JOBS e JOB_ADMIN_RUNNING OK.');
END;
/

PROMPT
PROMPT ── 6) Exemplos de Uso (para referência; copiar/colar quando necessário) …

PROMPT
PROMPT -- Inventário:
PROMPT BEGIN
PROMPT   pkg_job_admin.list_jobs;                          -- todos os jobs
PROMPT   pkg_job_admin.list_jobs('DEMOAPP');               -- por owner
PROMPT   pkg_job_admin.list_jobs(p_like => 'JOB_%');       -- por padrão (LIKE)
PROMPT END;
PROMPT /

PROMPT -- Em execução agora:
PROMPT BEGIN
PROMPT   pkg_job_admin.list_running_jobs;                       -- tudo
PROMPT   pkg_job_admin.list_running_jobs('DEMOAPP');            -- por owner
PROMPT   pkg_job_admin.list_running_jobs(p_like => 'SYNC_%');   -- por padrão (LIKE)
PROMPT END;
PROMPT /

PROMPT -- Operações pontuais:
PROMPT -- SCHEDULER (por nome; owner opcional se único no banco)
PROMPT -- BEGIN
PROMPT --   pkg_job_admin.disable_job('DEMOAPP.JOB_ATUALIZA_INDICES', p_stop_running => TRUE);
PROMPT --   pkg_job_admin.enable_job ('DEMOAPP.JOB_ATUALIZA_INDICES');
PROMPT -- END;
PROMPT -- /
PROMPT -- DBMS_JOB (por número; owner apenas para validar pertinência quando desejar)
PROMPT -- BEGIN
PROMPT --   pkg_job_admin.disable_job('128', p_owner => 'SYS', p_stop_running => TRUE);
PROMPT --   pkg_job_admin.enable_job ('128', p_owner => 'SYS');
PROMPT -- END;
PROMPT -- /

PROMPT -- Lote por padrão (por tipo):
PROMPT -- BEGIN
PROMPT --   pkg_job_admin.set_jobs_by_pattern(
PROMPT --     p_name_like    => 'JOB_SYNC_%',
PROMPT --     p_enable       => FALSE,
PROMPT --     p_owner        => 'DEMOAPP',
PROMPT --     p_type         => pkg_job_admin.c_type_scheduler, -- SCHEDULER | DBMS_JOB | ANY
PROMPT --     p_stop_running => TRUE
PROMPT --   );
PROMPT -- END;
PROMPT -- /

PROMPT -- Visões consolidadas:
PROMPT -- SELECT * FROM job_admin_all_jobs_v ORDER BY owner, job_type, job_name;
PROMPT -- SELECT * FROM job_admin_running_v  ORDER BY running_instance, job_type, job_name;

PROMPT
PROMPT ═════════════════════════════════════════════════════════════════════════════════════════════
PROMPT   Conclusão: job_admin_setup_completo.sql executado.
PROMPT   Para conceder a ROLE a um usuário final, utilize:
PROMPT     GRANT JOBDBMSSCHEDULERALL TO nome_do_usuario;
PROMPT ═════════════════════════════════════════════════════════════════════════════════════════════