In development, there are two MVs that sometimes tromp on each other. One MV is actively in development, the other is scheduled to refresh hourly. The hourly refresh is used in the MV in development.
When the scheduled MV refreshes successfully, it's in less than a minute. If there is contention, I see enq: JI - contention as a wait event. It waits for hours before failing with the ORA-54 error.
What I'd like is when the scheduled MV kicks off, if there is contention, fail right now. I'm confused because I thought the default was NOWAIT, which would mean just that - don't wait. I tried using DDL_LOCK_TIMEOUT but that's not what I want, and it doesn't make a difference.
This MV has these characteristics:
COMPRESS FOR DIRECT_LOAD OPERATIONS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
Does anyone know of a way to make this error immediately if it can't refresh?
Sherrie