CODEWITHSUNDEEP
oracle-databaseoracle10g
Learner
Learner

Reputation: 73

Need suggestion on Job run in Oracle 10g

I have a job running in ORacle 10g production DB which syncs two DB tables A and B. The job fetches data from table A and inserts into table B. The job runs on a daily basis and for the past few months it started failing in production with the error "Error in retrieving data -54". On checking the store procedure, I could see that the job fails due to locking record issue when other jobs lock the records from table A and our job is not able to process the same. So I started searching for some possible solutions which I have given below.

  1. Change the running time of the job so that it can process records. But this is not gonna help since table A is very critical and always used by production jobs. Also it has real time updates from the users.

  2. Instead of "No WAIT" use "SKIP LOCKED" so that job will skip the locked records and run fine. But problem here is if locked records(This is always negligible compared to the huge production data) are skipped, there will be mismatch in the data in tables A and B for the day. Next day run will clear this problem since the job picks unpicked records of previous days also.But the slight mismatch for the job failed days may cause small problems

  3. Let the job wait till all the records are unlocked and processed. but this again causes problem since we cannot predict how long job will be in waiting state(Long running state).

As of now one possible solution for me is to go with option 2 and ignore the slight deviation between table A and Bs data. Is there any other way in Oracle 10g Db to run the job without failing and long running and process all records. I wish to get some technical guidance on the same.

Thanks PB

Upvotes: 1

Views: 198

Answers (1)

Ben
Ben

Reputation: 1927

I'd handle the exception (note, you'll have to either initialise your own EXCEPTION or handle OTHERS and inspect the SQLCODE) and track the ids of the rows that were skipped. That way you can retry them once all the available records have been processed.

Something like this:

DECLARE

  row_is_locked EXCEPTION;
  PRAGMA EXCEPTION_INIT(row_is_locked, -54);

  TYPE t_id_type IS VARRAY(1) OF INTEGER;
  l_locked_ids t_id_type := t_id_type();

  l_row test_table_a%ROWTYPE;

BEGIN

  FOR i IN (
    SELECT a.id
    FROM test_table_a a
  )
  LOOP

    BEGIN

      -- Simulating your processing that requires locks
      SELECT *
      INTO l_row
      FROM test_table_a a
      WHERE a.id = i.id
      FOR UPDATE NOWAIT;

      INSERT INTO test_table_b 
      VALUES l_row;

      -- This is on the basis that you're commiting
      -- to release the lock on each row after you've
      -- processed it; may not be necessary in your case
      COMMIT;

    EXCEPTION

      WHEN row_is_locked THEN
        l_locked_ids(l_locked_ids.LAST) := i.id;
        l_locked_ids.EXTEND();

    END;

  END LOOP;

  IF l_locked_ids.COUNT > 0 THEN
    FOR i IN l_locked_ids.FIRST .. l_locked_ids.LAST LOOP
      -- Reconcile the remaining ids here
      NULL;
    END LOOP;
  END IF;

END;

Upvotes: 2

Related Questions