Trying to make a job to be executed after each 1 minute,but its not working?

Question

I have made this job,that should be executed in an interval of 1 minute,but it's not working. When I use execute dbms_job.run(2); it gets executed. printe is a procedure Please suggest!

BEGIN
    DBMS_JOB.SUBMIT  (
         job =>:job_no,
             WHAT=>'printe;',--Procedure 
             next_date=>sysdate+1/24*60,
             interval=>'sysdate+1/24*60'
          );
    commit;
END;

Answer 1

Try next_date = sysdate + (1/24/60) and interval = (1/24/60)...

Answer 2

Here is a simple job.

SQL> create table log1 (ts timestamp)
  2  /

Table created.

SQL> create or replace procedure printe as
  2  begin
  3      insert into log1 values (systimestamp);
  4      commit;
  5  end;
  6  /

Procedure created.

SQL>

So the first thing is to submit it with both the start time and interval correctly specified. If you cannot remember how many minutes there are in a day (1440) it is a good idea to use brackets. Let's compare submitting the job with your date specifications ...

SQL> var job_no number
SQL> BEGIN
   2     DBMS_JOB.SUBMIT
   3      (
   4      job =>:job_no,
   5      WHAT=>'printe;',--Procedure
   6      next_date=>sysdate+1/24*60,
   7    interval=>'sysdate+1/24*60'
   8    );
   9    commit;
  10  END;
  11  /

 PL/SQL procedure successfully completed.

 SQL> print job_no

     JOB_NO
 ----------
         71

 SQL>

... with brackets to assert precedence ...

SQL> BEGIN
  2     DBMS_JOB.SUBMIT
  3      (
  4      job =>:job_no,
  5      WHAT=>'printe;',--Procedure
  6      next_date=>sysdate+1/(24*60),
  7    interval=>'sysdate+1/(24*60)'
  8    );
  9    commit;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> print job_no

    JOB_NO
----------
        72

SQL>

Clearly job 71 has not run and isn't going to run for some time yet:

SQL>    select job, what, last_date, next_date, interval
  2  from user_jobs
  3  where job in (71,72)
  4  /

   JOB WHAT         LAST_DATE            NEXT_DATE            INTERVAL
------ ------------ -------------------- -------------------- -----------------
    71 printe;                           05-MAY-2010 17:35:34 sysdate+1/24*60
    72 printe;      03-MAY-2010 05:44:42 03-MAY-2010 05:45:34 sysdate+1/(24*60)

SQL>

Monitoring job 72 ....

SQL> select * from log1
  2  /

TS
-------------------------------------------------------------------
03-MAY-10 05:43:39.250000
03-MAY-10 05:44:42.296000

SQL>

So, if this still isn't working for you, what should you be doing? The first thing is to check whether the database is configured to run jobs at all. You will need DBA access for this.

SQL> select value
  2  from v$parameter
  3  where name='job_queue_processes'
  4  /

VALUE
-------------------------
1000

SQL>

If I remember correctly, in Oracle 9i the default value for this parameter is 0. It needs to be set to some non-zero value for jobs to run.

And if that isn't the problem you need to check for error messages in the alert log. The background_dump_dest directory may also have some .trc files produced by a failing job.