Home » RDBMS Server » Server Utilities » DBA_JOBS not getting executed.
DBA_JOBS not getting executed. [message #233782] Fri, 27 April 2007 08:06 Go to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hi,

Hi have a problem with my dba_jobs.

They have worked for at least a year now, but now they aren't being executed.

The job_queue_processes parameter is on 10.
SQL> SELECT NAME, value FROM v$parameter WHERE NAME LIKE '%job%';

NAME                     VALUE
---------                ---------
job_queue_processes      10



When I manually create a job, it shows up under dba_jobs, but is not executed.

SQL> DECLARE
  2  v_id NUMBER;
  3  v_job_what VARCHAR2(400);
  4  BEGIN
  5      v_job_what := 'begin dbms_output.put_line (''YO''); end;';
  6      Dbms_Job.submit(job => v_id, what => v_job_what, next_date => SYSDATE,
INTERVAL => NULL);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> SELECT job, what FROM dba_jobs WHERE last_date IS NULL;

       JOB   WHAT
----------   ---------------------------
      2355   begin dbms_output.put_line ('YO'); end;



If I manually start it, it's processed.

SQL> exec dbms_job.run(2355);

PL/SQL procedure successfully completed.

SQL> SELECT job, what FROM dba_jobs WHERE last_date IS NULL;

no rows selected

SQL>


Any Idea where the problem could be? I haven't found anything in the alert log or the dump directory.

Any way to check if the queue processes are indeed running process-wise on the Server maybe? My google-foo seems to be weak in that direction. Wink

Oracle Version :

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Server OS : AIX 5.3
Re: DBA_JOBS not getting executed. [message #233792 is a reply to message #233782] Fri, 27 April 2007 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to wait a little bit to see your job executed. It does not start just after your commit. Wait a minute.

Regards
Michel

Re: DBA_JOBS not getting executed. [message #233801 is a reply to message #233792] Fri, 27 April 2007 09:05 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, I only started to investigate once we found out that jobs hadn't been executed all day. Wink

in the meantime I have found out and tried :

The process on OS level can be found with

ps -eaf |grep cjq

which showed it running since this morning, when the DB was started.

I have now re-started it with

ALTER SYSTEM SET job_queue_processes = 0;
ALTER SYSTEM SET job_queue_processes = 10;

and it now shows under a new, just started process ID.

But still no execution, even after 5 minutes.

For now I have a work-around in place, running in one process :

BEGIN
  FOR i IN 1 .. 120 LOOP
     Dbms_Lock.sleep(60);
     BEGIN
         FOR l_jobs IN (SELECT * FROM dba_jobs WHERE last_date IS NULL)
         LOOP
         dbms_job.run(l_jobs.job);
         END LOOP;
     END;
  END LOOP;
END;
/


And will try to solve it by rebooting the entire DB once the users have left the building. Wink
Re: DBA_JOBS not getting executed. [message #233896 is a reply to message #233801] Sat, 28 April 2007 00:28 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
For the benefit of future forum searches :

It works again after the DB restart tonight.

It seems Metalink notes 102885.1 and 1026586.6 were right. They said basically :

"If you have checked job_queue_processes and the process on OS level, and it's still not working, then something really funny is going on with the job queue and you should restart the database"
Re: DBA_JOBS not getting executed. [message #233897 is a reply to message #233896] Sat, 28 April 2007 00:30 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for the feedback. Thumbs Up

Regards
Michel
Previous Topic: Script to recreate user before import of data
Next Topic: SQLplus Login Info display
Goto Forum:
  


Current Time: Wed Dec 25 12:21:53 CST 2024