2009-09-09

ORACLE JOBS

1 Views

  • SELECT * FROM dba_jobs
  • SELECT * FROM dba_jobs_running
  • column TOTAL_TIME determine the time which system spend run this job from first initialization, it is not time of present
  • column THIS_DATE is filled when job is still executing and show time present execution time

2 Create new job

declare
  jobnr number;
begin
  dbms_job.submit(
    job=>jobnr,
    what=>'dbms_output.put_line(''ble'');',
    next_date=>to_date('10:00 09/09/2009','HH24:MI DD/MM/YYYY'),
    interval=>'SYSDATE+1');
end;
/

3 Starting job by hand

  • must be run by job owner, otherwise ORA-23421, even for user sys, beware on change next_date which will by calculated from now
  • sql> exec DBMS_JOB.RUN(JOB => [nr]);

4 Turn off

  • set state BROKEN
  • sql> exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => TRUE);

5 Turn on

  • unset state BROKEN
  • sql> exec DBMS_JOB.BROKEN(JOB => [nr], BROKEN => FALSE, NEXT_DATE => TO_DATE(’10:00 01/01/09′, ‘HH24:MI MM/DD/YY’));

6 Changing job params

  • must be set all arguments, if any is unchange we set NULL:
  • sql> exec DBMS_JOB.CHANGE(JOB => [nr], NEXT_DATE => to_date('2011-01-01 10:00','YYYY-MM-DD HH24:MI'), WHAT => null, INTERVAL => ‘SYSDATE + 3′);

7 Removing job:

  • exec DBMS_JOB.REMOVE(JOB => [nr]);

8 dbms_ijob

  • DBMS_IJOB is a package included with Oracle that allows a sys or system user to manipulate other user's jobs.

9 How to set time:

sysdate+1from present time + 1day
trunc(sysdate)+1from 12.00 AM + 1day
trunc(sysdate)+17/2417:00 PM today<br />
trunc(sysdate)+60/14401:00 AM (60min/60min*24=1440)
  • sql> SELECT to_char(trunc(sysdate+1) + 90/1440, 'MM/DD/YYYY HH:MI AM') FROM dual;

10 ERRORs

10.1 ORA-23421

  • procedure dbms_job must by run by owner even sys user cant do that check dbms_ijob

10.2 ORA-12012

  • search above string ORA-12012 in alertlogs due to problem with previous jobs
Etykiety: ,