Home » SQL & PL/SQL » SQL & PL/SQL » Problem with DBMS_JOB
Problem with DBMS_JOB [message #389484] Mon, 02 March 2009 05:32 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
HI,

I Created below procedure and scheduled it to execute for ecery 30 secs.

But as per my schedule its not able to schedule for every 30 secs.

I am not able to see any data in samp table.


create or replace procedure test_job as
 begin
 for i in 1..1000
 loop
 insert into samp values(i);
 end loop;
 commit;
 end;


declare
  my_job number;
begin
  dbms_job.submit(job => my_job, 
    what => 'test_job;',
    next_date => sysdate,
    interval => 'SYSDATE + 30/86400');
commit;
end;



Any help really appreciated.

Thanks in advance
Re: Problem with DBMS_JOB [message #389485 is a reply to message #389484] Mon, 02 March 2009 05:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you see in the USER_JOBS view?
Re: Problem with DBMS_JOB [message #389487 is a reply to message #389484] Mon, 02 March 2009 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
If I've got a job that isn't behaving as expected the first thing I look at is user_jobs. So what does it say about your job?

EDIT: - Too Slow!

[Updated on: Mon, 02 March 2009 05:38]

Report message to a moderator

Re: Problem with DBMS_JOB [message #389490 is a reply to message #389484] Mon, 02 March 2009 05:45 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Hello,

As per your table and procedure. I think your job running fine.

SQL> select count(*) from sample;

  COUNT(*)
----------
        50

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:12:48

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:12:51

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:13:10

SQL> select count(*) from sample;

  COUNT(*)
----------
        60

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:13:48

SQL> select count(*) from sample;

  COUNT(*)
----------
        70


>>But as per my schedule its not able to schedule for every 30 secs.

Confirm us.

Babu
Re: Problem with DBMS_JOB [message #389496 is a reply to message #389490] Mon, 02 March 2009 05:56 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

I am not able to see any count in samp table.

Do I need to do anything before or after executing.

Pls find my analysis.



SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:21:34

SQL> select count(*) from samp;

  COUNT(*)
----------
         0

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:21:54

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:22:00

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:22:06

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:22:11

SQL> select count(*) from samp;

  COUNT(*)
----------
         0

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:22:15

SQL> select count(*) from samp;

  COUNT(*)
----------
         0

SQL> select count(*) from samp;

  COUNT(*)
----------
         0

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:22:21

SQL> select count(*) from samp;

  COUNT(*)
----------
         0

SQL> select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-mar-2009 05:22:26

SQL>



Thanks in advance
Re: Problem with DBMS_JOB [message #389498 is a reply to message #389485] Mon, 02 March 2009 05:58 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Mon, 02 March 2009 11:36
What do you see in the USER_JOBS view?

Re: Problem with DBMS_JOB [message #389499 is a reply to message #389498] Mon, 02 March 2009 06:00 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

I am looking for below data.


SQL> select last_date, last_sec, next_date, next_sec,interval from user_jobs;

LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC
--------- -------- --------- --------
INTERVAL
--------------------------------------------------------------------------------

02-MAR-09 17:12:01 02-MAR-09 17:12:31
SYSDATE + 30/86400



Pls let me know whether I am correct or not.

Thanks in advance
Re: Problem with DBMS_JOB [message #389501 is a reply to message #389484] Mon, 02 March 2009 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
That would imply it's running every 30 secs.

What does the failures column of user_jobs show?

If you run your procedure from sqlplus does data get inserted?
Re: Problem with DBMS_JOB [message #389502 is a reply to message #389501] Mon, 02 March 2009 06:23 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Yes, I am able to insert data through sqlplus.

Here the query result.


SQL> select last_date, last_sec, next_date, next_sec,interval, failures from use
r_jobs;

LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC
--------- -------- --------- --------
INTERVAL
--------------------------------------------------------------------------------

  FAILURES
----------
02-MAR-09 17:40:11 02-MAR-09 17:40:41
SYSDATE + 30/86400
         0



Thanks in advance
Re: Problem with DBMS_JOB [message #389503 is a reply to message #389484] Mon, 02 March 2009 06:29 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you got more than one table called samp in your database?
Re: Problem with DBMS_JOB [message #389505 is a reply to message #389503] Mon, 02 March 2009 06:36 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

No, I am having only samp table in Database.

Thanks for your reply
Re: Problem with DBMS_JOB [message #389510 is a reply to message #389484] Mon, 02 March 2009 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm baffled then.

You've got a procedure that inserts a 1000 records.
You've got a job that runs this procedure every 30 seconds.

It appears the job is running every 30 seconds.
It doesn't appear to have encounted any errors.
There doesn't appear to be an exception handling in your procedure that masks errors.

So there should be data there.

Only possibilities I can think of are:
1) There is another process running that's deleting the data.
2) That job isn't running the procedure you think it is (check user_jobs.what)
3) That procedure isn't inserting into the table you think it is (hence my previous question)
4) There's some exception handling code you haven't told us about that's masking errors.
Re: Problem with DBMS_JOB [message #389511 is a reply to message #389502] Mon, 02 March 2009 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if job_queue_processes is greater than 0.

Regards
Michel
Re: Problem with DBMS_JOB [message #389518 is a reply to message #389510] Mon, 02 March 2009 07:04 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


1/ Please confirm us your database version (4 digit)

2/ SQL> select job,log_user,priv_user,schema_user,last_Date,last_sec,this_Date,this_sec,nexT_date,next_sec,broken,failures,
interval,what from user_jobs;

Post the above query output.



[Updated on: Mon, 02 March 2009 07:06]

Report message to a moderator

Re: Problem with DBMS_JOB [message #389519 is a reply to message #389518] Mon, 02 March 2009 07:19 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below is the version of oracle and the output of query requested.




SQL> select * from v$version
  2  ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



SQL> select job,log_user,priv_user,schema_user,last_Date,last_sec,this_Date,this
_sec,nexT_date,next_sec,broken,failures,
  2  interval,what from user_jobs;

       JOB LOG_USER                       PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER                    LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
------------------------------ --------- -------- --------- -------- ---------
NEXT_SEC B   FAILURES
-------- - ----------
INTERVAL
--------------------------------------------------------------------------------

WHAT
--------------------------------------------------------------------------------

        22 VIKRAM                         VIKRAM
VIKRAM                                                               02-MAR-09
18:09:34 N

       JOB LOG_USER                       PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER                    LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
------------------------------ --------- -------- --------- -------- ---------
NEXT_SEC B   FAILURES
-------- - ----------
INTERVAL
--------------------------------------------------------------------------------

WHAT
--------------------------------------------------------------------------------

SYSDATE + 30/86400
vikram.test_job;


SQL>




Thanks for your reply
Re: Problem with DBMS_JOB [message #389520 is a reply to message #389519] Mon, 02 March 2009 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 02 March 2009 13:51
Check if job_queue_processes is greater than 0.

Regards
Michel


Re: Problem with DBMS_JOB [message #389529 is a reply to message #389520] Mon, 02 March 2009 08:35 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Hi,

As per Mr Mic; job_queue_processes should be Zero ( It's default value)

Please check

SQL> show user
USER is "SYS"
SQL>
SQL>
SQL> show parameter job


If it;s Zero;

1/ Try to Increase Upto 4-10

Using

alter system set job_queue_processes = 10 scope=[B]spfile Or pfile[/B]


2/ If it's require re-start your database

3/ After 30 min; check your table
Re: Problem with DBMS_JOB [message #389532 is a reply to message #389529] Mon, 02 March 2009 08:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd be very suprised if the job_queue_processes was 0, as the next run date has been changing in each of the results that the OP has posted.
The thing that is odd about the last set of results from user_jobs is that the FAILURES column isn't present in the results.
Previous Topic: Problem with LAG function
Next Topic: systimestamp conversion
Goto Forum:
  


Current Time: Tue Dec 06 15:52:54 CST 2016

Total time taken to generate the page: 0.14429 seconds