Oracle Job Schedulers examples

Oracle Job Schedulers examples

The following code examples rely on the previously defined programs and schedules to show how the overloads of the create_job procedure are used.
BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
BEGIN
  -- Job defined by an existing program and schedule.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_job_definition',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule.');
END;
/

Get a list of MViews depending on table using DBMS_MVIEW.GET_MV_DEPENDENCIES

DECLARE
   v_output   VARCHAR2 (2000);
   v_owner    VARCHAR2 (30);
   v_table    VARCHAR2 (30);
BEGIN
   v_owner := UPPER ('&&ENTER_OWNER_NAME');
   v_table := UPPER ('&&ENTER_TABLE_NAME');
   DBMS_MVIEW.GET_MV_DEPENDENCIES (v_owner || '.' || v_table, v_output);
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line
      ('Materialized Views Dependent on table      &&ENTER_OWNER_NAME..&&ENTER_TABLE_NAME'
      );
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line (v_output);
END;
/

HOW TO Kill The Running Job & session in Oracle

1. Check the running Job (From here you will get the SID that running the Job)

SQL> SELECT * FROM DBA_JOBS_RUNNING;

2. Make Job become Broken/offline

BEGIN
SYS.DBMS_JOB.BROKEN(job#,TRUE);
END;

SQL>
BEGIN
SYS.DBMS_IJOB.BROKEN('136451',TRUE);
END;

3. Kill the Oracle’s Session

SELECT SID,SERIAL# FROM v$session where sid in (SELECT sid from dba_jobs_running)

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process ID (PID)

SELECT p.spid FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid = :sid;
 
5. Check if the Job is Still Running

SQL> SELECT * FROM DBA_JOBS_RUNNING;

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue

SQL> SELECT * FROM DBA_JOBS_RUNNING;

9. Mark the DBMS_JOB as Not Broken

BEGIN SYS.DBMS_IJOB.BROKEN(job#,FALSE); END;

10. Alter the Job Queue to Original Value

SQL> ALTER SYSTEM SET job_queue_processes = original_value;


Source.
http://levicorp.com/2009/05/22/how-to-kill-the-running-job/

Thanks.