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.