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
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?
Here is an idea (never tried it in
MV context though):
Find out what objects are locked by
the refresh. Those Metalink notes can help:
MATERIALIZED VIEW REFRESH: Locking,
Performance, Monitoring (Doc ID 258252.1)
Monitoring Locks During Materialized View Refreshes (Doc ID 258258.1)
Lock explicitly (LOCK TABLE <Tab> NOWAIT or DML type lock) with
NOWAIT the objects that are going to be locked by the MV refresh.
If all objects are locked successfully then the MV refresh should be able
to proceed without locking issues.
If not, you would know right away.
Thanks for the suggest Iordan. Maybe I wasn't clear on my question.
I know what object are locked. My problem is two MVs are refreshing at the same time - MV A is refreshing, he depends on MV B. Then, MV B kicks off a refresh; he waits for 7 or more hours before the resource busy error returns. I'd like for MV B to try to refresh, as soon as he sees a resource is busy, bail out. I'd know there is an error in less than a minute.
I understood you correctly.
First, I do not have a simple answer – I am not aware of a
flag or a setting that can directly solve your problem.
Currently, the refresh of MV B hangs because the session
waits to get a lock of resource “X”. That resource “X” is held by the session
that performs MV A refresh, so the MV B refresh session has to wait a long
What I am suggesting is to lock (with NOWAIT) the resource “X”
explicitly before you start the refresh of MV B. If you are able to do that,
you can start MV B refresh. If the resource is held by other session (MV A
session for instance) then the explicit lock should fail immediately, so you
can “bail out” right away.