Oracle Jobs

[andy1671]

We have two Oracle 9.2 Servers and when we publish jobs to both servers they both report that the jobs have completed with no errors, but when we examine the tables it is obvious that the jobs are not being executed.

Does anyone have any idea what is going on here?

 

[carp]

Do the jobs commit?
Also, do you have exception sections that clean up after an exception is raised (including a possible rollback) if something goes wrong but leave no messages? If so, then it IS completed successfully, but no changes will be apparent.
Is it possible that nothing was supposed to happen (e.g., there is an UPDATE involved, but no rows meet the WHERE criteria)?

Without more information, it's rather hard to diagnose! I would recommend you modify your jobs to leave an audit trail of what occurred during execution. At the very least, it should tell you how many rows were inserted/updated/deleted, and all exception handlers should write a message telling you what occurred and what was done.

 

[andy1671]

The jobs do commit,

The test procedure I am using is as basic as you can get and is shown below:

as
begin
delete from ae;
insert into ae (debug_col) values ('Table Updated');
commit;
end;

Where debug_col is a varchar2(100).

 

[carp]

Yep - that's pretty basic all right!
How are you publishing the jobs?

 

[andy1671]

Via the Oracle Manager Server.

These jobs have been working file upto the middle of last week when we noticed that the new jobs or the modified jobs were not working.

 

[iamtrying]

Review your job's target, tasks, parameters, scheduling, and access rights. If you have many, they can be altered unknowingly. Also, look at the Show Output file within OEM, it will tell you exactly what happen with the job.

 

[andy1671]

None of the job's targets, parameters etc have been changed.

No errors are reported in the Show Output from the OEM as shown below:

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 30 17:22:04 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> 2 3 Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production