DBMS Jobs

select job_name, state,enabled, failure_count, max_failures from dba_scheduler_jobs where failure_count>0;

select job_name, enabled,run_count, failure_count, last_start_date from user_scheduler_jobs where job_name='GETNETHEALTHORDER';

 Fixing Jobs

Using the package DBMS_SCHEDULER one can enable/disable jobs.

  --- Disable job: This disables the job from running

SQL> exec dbms_scheduler.disable('GETNETHEALTHORDER');

PL/SQL procedure successfully completed.

— check job status

SOACONFIG.GETNETHEALTHORDER

SQL> select job_name, enabled from user_SCHEDULER_JOBS WHERE job_name = 'GETNETHEALTHORDER';

JOB_NAME ENABL

————————- —–

GATHER_STATS_JOB FALSE

---  Enable job:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

— check job status

SQL> select owner,job_name, enabled,run_count, failure_count, last_start_date from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL

————————- —–

GATHER_STATS_JOB TRUE

BEGIN

   dbms_scheduler.run_job('UPDATE_PLAYER_STATES');

END;

show parameter job_queue

    -- Job queue must be greater than zero .

Get Job DDL
select dbms_metadata.get_ddl('PROCOBJ','MY_JOB','IARSOV') from dual;

Job  History

SELECT l.log_id "Log ID",

         TO_CHAR (l.log_date, 'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm') "Log Date",

         l.operation "Operation",

         l.status "Status",

         l.user_name "User Name",

         l.client_id "Client ID",

         l.global_uid "Global UID",

         TO_CHAR (r.req_start_date, 'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')

            "Required Start Date",

         TO_CHAR (r.actual_start_date, 'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')

            "Actual Start Date",

         TO_CHAR (r.run_duration) "Run Duration",

         r.instance_id "Instance ID",

         r.session_id "Session ID",

         r.slave_pid "Slave PID",

         TO_CHAR (r.cpu_used) "CPU Used",

         r.additional_info "Additional Info (Run)"

    FROM dba_scheduler_job_log l, dba_scheduler_job_run_details r

   WHERE     l.owner = 'IONPROGRESS'

         AND l.job_name = 'DIGIWEBSERVICECALL'

         AND l.log_id = r.log_id(+)

ORDER BY 1 DESC