Home » SQL & PL/SQL » SQL & PL/SQL » Job not being executed on time (Oracle 11g r2 , Linux OEM 5.9)
| Job not being executed on time [message #611704] |
Mon, 07 April 2014 09:02  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I am trying to create a job to be ran every 1 minute, and I seem to be able to create it, however,
When I check - it wasn't ran although it's due time has been reached and passed:
SQL> show user
USER is "SYS"
SQL>
SQL> drop user dba_user cascade;
User dropped.
SQL>
SQL> create user dba_user identified by dbpass;
User created.
SQL> grant dba to dba_user;
Grant succeeded.
SQL> conn dba_user/dbpass@my_db
Connected.
SQL>
SQL>
SQL>
SQL> set serveroutput on
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';
Session altered.
SQL> set num 4
SQL> col schema_user for a10
SQL> col last_date for a5
SQL> col this_date for a5
SQL> col next_date for a10
SQL> col interval for a10
SQL> col what for a18
SQL> col sysdate for a10
SQL>
SQL>
SQL>
SQL> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
no rows selected
SQL>
SQL>
SQL>
SQL> declare
2 JobNo user_jobs.job%TYPE;
3 BEGIN
4 dbms_job.submit(job => JobNo,
5 what => 'begin execute immediate ''create table job_done as select * from dual''; end;',
6 next_date => SYSDATE,
7 interval => 'SYSDATE + 1/1440');
8 dbms_output.put_line(chr(10)||'Job No. is: '||JobNo||',Should run this job in 1 minute
(at '||to_char(sysdate+ 1/1440)||')');
9 COMMIT;
10 END;
11 /
Job No. is: 7145,Should run this job in 1 minute (at 07.04.2014 13:48)
PL/SQL procedure successfully completed.
SQL>
SQL> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
SYSDATE JOB SCHEMA_USE LAST_ THIS_ NEXT_DATE INTERVAL WHAT
---------- ---- ---------- ----- ----- ---------- ---------- ------------------
07.04.2014 7145 DBA_USER 07.04.2014 SYSDATE + begin execute imme
13:47 13:47 1/1440 diate 'create tabl
e job_done as sele
ct * from dual'; e
nd;
SQL>
SQL>
SQL> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
SYSDATE JOB SCHEMA_USE LAST_ THIS_ NEXT_DATE INTERVAL WHAT
---------- ---- ---------- ----- ----- ---------- ---------- ------------------
07.04.2014 7145 DBA_USER 07.04.2014 SYSDATE + begin execute imme
13:51 13:47 1/1440 diate 'create tabl
e job_done as sele
ct * from dual'; e
nd;
SQL>
SQL> select * from job_done;
select * from job_done
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
If I run it with DBMS_JOBS.RUN() it will work,however, not automatically by the scheduled time.
Does anyone know what I did wrong here?
Thanks in advance,
Andrey R.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Job not being executed on time [message #611710 is a reply to message #611709] |
Mon, 07 April 2014 10:11  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 07 April 2014 18:00
Check alert.log
Post value of parameter job_queue_processes.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 4
SQL>
SQL>
SQL> select * from dba_jobs_running;
no rows selected
SQL>
I will check alert log soon and post the latest content.
|
|
|
|
|
|
| Re: Job not being executed on time [message #611723 is a reply to message #611704] |
Tue, 08 April 2014 00:29 |
 |
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
|
Junior Member |
|
|
Comment on your code - the job's first execution according to the parameters is SYSDATE. And not (SYSDATE + 1 minute). The interval is applied to determine the job's next execution. Not its first execution time (that is governed by the NEXT_DATE parameter).
I have seen this type of problem on 10g - where a job scheduled seemingly correct does not get run. Workaround was to remove the job and submit it again.
[Updated on: Tue, 08 April 2014 00:33] Report message to a moderator |
|
|
|
| Re: Job not being executed on time [message #611729 is a reply to message #611713] |
Tue, 08 April 2014 03:11 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 07 April 2014 19:03
Also check parameter _job_queue_interval
SQL> col name for a20
SQL> col value for a10
SQL> col description for a40
SQL> select a.ksppinm name,
2 b.ksppstvl value,
3 a.ksppdesc description
4 from
5 sys.x$ksppi a,
6 sys.x$ksppcv b
7 where
8 a.indx = b.indx
9 and
10 a.ksppinm like '\_%' escape '\'
11 and lower(a.ksppinm) like '%_job_queue_interval%'
12 order by
13 name;
NAME VALUE DESCRIPTION
-------------------- ---------- ----------------------------------------
_job_queue_interval 5 Wakeup interval in seconds for job queue
co-ordinator
|
|
|
|
| Re: Job not being executed on time [message #611730 is a reply to message #611723] |
Tue, 08 April 2014 03:12 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
vslabs wrote on Tue, 08 April 2014 08:29Comment on your code - the job's first execution according to the parameters is SYSDATE. And not (SYSDATE + 1 minute). The interval is applied to determine the job's next execution. Not its first execution time (that is governed by the NEXT_DATE parameter).
I have seen this type of problem on 10g - where a job scheduled seemingly correct does not get run. Workaround was to remove the job and submit it again.
So, you say that if I run the job with DBMS_JOB.RUN() - I can wait for another minute and see that it would run again?
Or how do I check this?
|
|
|
|
|
|
| Re: Job not being executed on time [message #612693 is a reply to message #612675] |
Wed, 23 April 2014 05:56 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> drop user dba_user cascade;
User dropped.
SQL> create user dba_user identified by dbpass;
User created.
SQL> grant dba to dba_user;
Grant succeeded.
SQL> conn dba_user/dbpass
Connected.
DBA_USER> set serveroutput on
DBA_USER> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';
Session altered.
DBA_USER> set num 4
DBA_USER> col schema_user for a10
DBA_USER> col last_date for a5
DBA_USER> col this_date for a5
DBA_USER> col next_date for a10
DBA_USER> col interval for a10
DBA_USER> col what for a30
DBA_USER> col sysdate for a10
DBA_USER> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
no rows selected
DBA_USER> declare
2 JobNo user_jobs.job%TYPE;
3 BEGIN
4 dbms_job.submit(
5 job => JobNo,
6 what => 'begin execute immediate ''create table job_done as select sysdate dt from dual''; end;',
7 next_date => SYSDATE,
8 interval => 'SYSDATE + 1/1440');
9 dbms_output.put_line(
10 chr(10)||'Job No. is: '||JobNo||',Should run this job in 1 minute (at '||
11 to_char(sysdate+ 1/1440)||')');
12 COMMIT;
13 END;
14 /
Job No. is: 82,Should run this job in 1 minute (at 23.04.2014 12:48)
PL/SQL procedure successfully completed.
DBA_USER> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
SYSDATE JOB SCHEMA_USE LAST_ THIS_ NEXT_DATE INTERVAL WHAT
---------- ---- ---------- ----- ----- ---------- ---------- ------------------------------
23.04.2014 82 DBA_USER 23.04.2014 SYSDATE + begin execute immediate 'creat
12:47 12:47 1/1440 e table job_done as select sys
date dt from dual'; end;
1 row selected.
DBA_USER> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
DBA_USER> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
SYSDATE JOB SCHEMA_USE LAST_ THIS_ NEXT_DATE INTERVAL WHAT
---------- ---- ---------- ----- ----- ---------- ---------- ------------------------------
23.04.2014 82 DBA_USER 23.04.2014 SYSDATE + begin execute immediate 'creat
12:49 12:50 1/1440 e table job_done as select sys
date dt from dual'; end;
1 row selected.
DBA_USER> select * from job_done;
select * from job_done
*
ERROR at line 1:
ORA-00942: table or view does not exist
The job has been executed but the table is not created.
Have a look at alert.log:
ORA-12012: error on auto execute of job 82
ORA-01031: insufficient privileges
ORA-06512: at line 1
So:
SQL> grant create table to dba_user;
Grant succeeded.
SQL> conn dba_user/dbpass
Connected.
DBA_USER> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
SYSDATE JOB SCHEMA_USE LAST_ THIS_ NEXT_DATE INTERVAL WHAT
---------- ---- ---------- ----- ----- ---------- ---------- ------------------------------
23/04/2014 82 DBA_USER 23/04/2014 SYSDATE + begin execute immediate 'creat
12:51:17 12:51:23 1/1440 e table job_done as select sys
date dt from dual'; end;
1 row selected.
MIKB2 DBA_USER> exec dbms_lock.sleep(120);
PL/SQL procedure successfully completed.
MIKB2 DBA_USER> select sysdate,job, schema_user, last_date, this_date, next_date,interval,what
2 from user_jobs;
SYSDATE JOB SCHEMA_USE LAST_ THIS_ NEXT_DATE INTERVAL WHAT
---------- ---- ---------- ----- ----- ---------- ---------- ------------------------------
23/04/2014 82 DBA_USER 23/04 23/04/2014 SYSDATE + begin execute immediate 'creat
12:54:07 /2014 12:54:23 1/1440 e table job_done as select sys
12:5 date dt from dual'; end;
1:23
1 row selected.
MIKB2 DBA_USER> select * from job_done;
DT
-------------------
23/04/2014 12:51:23
1 row selected.
My first answer was:
Quote:Check alert.log
And your answer:
Quote:I will check alert log soon and post the latest content.
But we are still waiting for it...
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 09:53:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
