Hi tom,
sorry tom,once again i come to you on other thread.
MY production DB running on 9.2.0.1.0, one day when 30 users were connected ,i started gather schema stats.

So the alert logfile has shown message like
---------------alert log file--------------------------
Fri Oct 29 20:16:02 2004
ORA-01555 caused by SQL statement below (Query Duration=4938 sec, SCN: 0x0000.0d2ef122):
Fri Oct 29 20:16:02 2004
SELECT COMPANYID, comp_appl_id from lot_workflowstage_dtl where
stage = 'DDUPEXEC' and stagestatus = 'P'
order by comp_appl_id
Fri Oct 29 20:16:02 2004
Errors in file /oracledb/oracledata/admin/RFES/bdump/rfes_j000_4510.trc:
ORA-12012: error on auto execute of job 7
ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small
ORA-06512: at "SCB.PROC_RUN_DEDUP_BATCH", line 12
ORA-06512: at line 3
Fri Oct 29 20:26:43 2004
------------------------------------------------------
then i cut down gather schema stats.
job 7 has following procedure.........
************TEXT
--------------------------------------------------------------------------------
in_comp_appl_id number(8) ;
begin
for x in cur_rec loop
PROC_DEDUP_MATCH(x.COMPANYID, x.comp_appl_id, null, null, null, out_val);
out_val := pkgworkflow.updateworkflow(x.COMPANYID, x.comp_appl_id, null, '
DDUPEXEC', null, null, 'SYSTEMI', 'C', dummyval1, dummyval2, dummyval3);

if out_val is not null then
rollback;
insert_error(x.comp_appl_id,'Workflow Error Occured : ' || out_val, null
,'Job Scheduler for Dedup Process');

TEXT
--------------------------------------------------------------------------------

delete from lot_workflowstage_dtl
where stage in ('DDUPEXEC') and stagestatus = 'P'
and (comp_appl_id,stage) in
(select comp_appl_id,stage from lot_workflowstage_dtl where
stage = 'DDUPEXEC' and comp_appl_id = x.comp_appl_id
group by comp_appl_id,stage
having count(*) > 1 );

delete from lot_workflowstage_dtl
where stage in ('STYMFILE') and stagestatus = 'P'

TEXT
--------------------------------------------------------------------------------
and (comp_appl_id,stage) in (select comp_appl_id,stage from lot_wo
rkflowstage_dtl where

stage = 'STYMFILE' and comp_appl_id = x.comp_appl_id
group by comp_appl_id,stage
having count(*) > 1 );

commit;
else
commit;
end if;

TEXT************************


Then i tried to shutdown the database then ora_j000_rfes is hanging.SO i killed that background process .then db shutdown immediately.
Afer startup also the count on lot_workflowstage_dtl running very slow .it is showing uncompleted jobs lot.

then i rebuilt the indexes and shutdown db (by killing the ora_j000_sid) as well as manully kept job 7 in broken status and rerun .
SO that its running fine now.

could you guess the problem which was raised and pls give me the solution to avoid in future.
thanks in advance.

We want to be able to suspend all the jobs that are running for a perticular schema during upgrade of application and de-suspend all after the upgrade is done. We are using oracle 8.1.7.4, 9i and 10g.

I wanted to keep it simple so I used what you have suggested
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; to suspend all the queued jobs, but the job which was in already running did not get suspended. Any easy way to do that ?

Thanks