Jobs

Jobs

Jobs running in the database:
------------------------------------------------------------------------------

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, 
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, 
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j; 

scheduled_dbms_jobs.sql
------------------------------------------------------------------------------
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, 
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, 
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;


running_jobs.sql
----------------------------------------------------------------------------


set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID, 
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, 
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, 
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;


session_jobs.sql
---------------------------------------------------------------------------


set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID, 
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, 
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, 
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

-------------------------------------------------------------------------------------
Jobs Running and Broken Jobs:
------------------------------------------------------------------------------------------

This script shows which jobs are currently executing, who owns them, and when they began:

/* Filename on companion disk: job2.sql */*
col job_definition format a30 word_wrap
col username format a15

ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';

SELECT  jr.job  job_id,username username ,jr.this_date start_date,what  job_definition
FROM dba_jobs_running  jr ,dba_jobs j,v$session s
WHERE  s.sid  = jr.sid
AND  jr.job = j.job   ORDER BY jr.this_date;

----------------------------------------------------------
BROKEN JOBS:

The following script shows failing or broken jobs (i.e., jobs that may need attention):

/* Filename on companion disk: job2.sql */*
col job_owner format a15
col job_definition format a30 word_wrap

SELECT  job          
       ,log_user     job_owner
       ,failures
       ,broken
       ,what         job_definition
  FROM
        dba_jobs
 WHERE
        broken = 'Y' OR NVL(failures,0) > 0 ;

------------------------------------------------------------------

---------------------------------------------------------------------------
-- Schedule the job using the DB Job System. This section can be removed if 
-- the job is sceduled via an external scheduler.
---------------------------------------------------------------------------


declare
  v_job number;
begin
  select job into v_job from user_jobs where what like 'db_space_hist_proc%';
  dbms_job.remove(v_job);
  dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
                        'sysdate+7');   -- Run every 7 days
  dbms_job.run(v_job);
  dbms_output.put_line('Job '||v_job||' re-submitted.');
exception
  when NO_DATA_FOUND then
       dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
                      'sysdate+7');   -- Run every 7 days
       dbms_job.run(v_job);
       dbms_output.put_line('Job '||v_job||' submitted.');
end;
/
----------------------------------------------------------------------------

-- Schedule a snapshot to be run on this instance every hour

variable jobno number;
variable instno number;
begin

select instance_number into :instno from v$instance;
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour
-- ------------------------------------------------------------
dbms_job.submit(
:jobno, 'statspack.snap;', 
trunc(sysdate)+6/24, 
'trunc(SYSDATE+1/24,''HH'')', 
TRUE, 
:instno);

-- ------------------------------------------------------------
-- Submit job to begin at 0900 and run 12 hours later
-- ------------------------------------------------------------
dbms_job.submit(
:jobno, 
'statspack.snap;', 
trunc(sysdate+1)+9/24, 
'trunc(SYSDATE+12/24,''HH'')', 
TRUE, 
:instno);

-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every 10 minutes
-- ------------------------------------------------------------
dbms_job.submit(
:jobno, 
'statspack.snap;',
trunc(sysdate+1/144,'MI'),
'trunc(sysdate+1/144,''MI'')', 
TRUE, 
:instno);

-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno, 
'statspack.snap;', 
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE,''MONDAY''), 
next_day(SYSDATE,''TUESDAY''), 
next_day(SYSDATE,''WEDNESDAY''), 
next_day(SYSDATE,''THURSDAY''), 
next_day(SYSDATE,''FRIDAY'') 
)
+1/24,''HH'')', 
TRUE, 
:instno);

commit;
end;
/ 

-------------------------------------------------------------------------
VARIABLE jobno number
 exec DBMS_JOB.SUBMIT(JOB=> :jobno,WHAT=> 'dbms_refresh.refresh(''"BPADB"."BPA_DAILY_DISPUTE_REPORT"'');',NEXT_DATE=>TO_DATE('2009-07-01:00:11:00','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=> 'SYSDATE+1',NO_PARSE=> TRUE);

-------------------------------------------------------------------------------------
METADATA OF A JOB:
-------------------------------------------------------------------------------------

select dbms_metadata.get_ddl('JOB','BPADB') from dual;
 
-----------------------------------------------------------------------------------

DROP JOB:

-----------------------------------------------------
exec dbms_job.remove(87);
------------------------------------------------------