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

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
abhilashnair
Participant
Posts: 284
Joined: Fri Oct 13, 2006 4:31 am

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

Post by abhilashnair »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
abhilashnair
Participant
Posts: 284
Joined: Fri Oct 13, 2006 4:31 am

Post by abhilashnair »

Did a workaround setting up dependency between the 2 jobs
Post Reply