weird issues with large# of jobs

We have application that changes every year, but we have old version running and being used as well.

The changes are mostly data changes, and database structure and procedures are in general the same.

The application requires 4 jobs to run every night around 10pm to save the 'state' of certain tables. Most of the jobs just drop some table called <tab>_cache, recreate index if there is one and do some logging along the way. Since the application is fairly old, we use DBMS_SQL for all DDL.

We recently hit a # where are running more than 50 of such jobs at once. Some jobs mysteriously fail, some don't run at all now.

The following errors get my suspecting that the code somehow is executing against other schemas, as the code is identical amongst all schemas:

STRT

ORA-12012: error on auto execute of job 16
ORA-01418: specified index does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "STRT_2002.SAVE_LINEUP", line 32
ORA-06512: at line 1
ORA-12012: error on auto execute of job 530
ORA-01418: specified index does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "STRT_2002.SAVE_LINEUP", line 32
ORA-06512: at line 1

However,
select job, log_user, what from user_jobs:

system_at_STRT> select job, log_user, what from dba_jobs where job in (16,530);

    JOB LOG_USER WHAT
------- ---------------

    530 STRT_FS save_lineup;
     16 STRT_2002 save_lineup;

This is 9.2.0.6 on RH4.0, we only started having this issue once we started running with so many jobs.

.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Mon Apr 17 2006 - 15:56:31 CDT