DSXchange

We have a scenario with two ETL jobs(Lets call them Job A and Job B) belonging to different subject areas with no dependency among each other, but operating on same table.

job A has the following statement

ALTER TABLE <TABLENAME> TRUNCATE PARTITION (PNAME) UPDATE INDEXES

job B does a simple select * from the same table above

Now the issue is this. On rare occasions they kind of overlap or run simultaneously causing the below error in second batch job (which has the select statement)

Error message: ORA-08103: object no longer exists.

We are aware that the root cause is because it is trying to select from the table when the partition is being truncated. We even were able to reproduce it in the TEST environment where we ran the job B and on a separate toad session we ran the Truncate Partition query at the same time

As I said this happens on only once or twice a month when these jobs run simultaneously. This is completely not in our control as we cant set dependencies between these 2. As i said they belong to different domains and we cant just make 1 job wait for another

In this case, is there something which can be done in the Job B , oracle connector stage SELECT query such that this error does not happen ? Something which will delay the Select thus not causing the job to abort?

Version Info:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

I'm not aware of anything you can do inside the connector to "delay the select" when the object is in the midst of not existing. But you could certainly do something outside of it.

First thing that popped into my head was a simple semaphore / in-use mechanism that Job A sets and unsets and which Job B can check for. This could be a simple as an empty flat file on the server or a record in a table both can access. Job A creates it when it starts and removes (or updates) it when it completes. Something (sequence job?) checks for the presence of this blocking object and does a looping check until it is released before it allows Job B to start. You could also establish something similar for Job B so that Job A doesn't run while Job B is running.

We've used this successfully over the years (both ways) to avoid situations like you describe or to simply stop a new instance of a very long running process (several hundred jobs) from being started if for some reason the previous iteration hasn't finished yet.

See this topicfor an auto-retry workaround to a similar issue that you could use as well. This can be done at the sequence job level.

Did a workaround setting up dependency between the 2 jobs