Am I able to find out the row cache lock holder only through V$ views? The answer is YES and it’s what V$ROWCACHE_PARENT view is meant for.

Let me explain with simple test case.

Create a sequence object with NOCACHE attribute and let two sessions invoke the sequence.nextvalue function recursively. Oracle needs to hold the row cache lock in 5(SRX) mode when calling NEXTVALUE on the nocached sequence, which means it’s very easy to replay the row cache lock contention.

Thanks so much for this small piece of SQL. It was 4 AM here and got a call from office that most of the sessions were going in “row cache lock” wait, was Googling for it and came across your post. So helpful it was for identifying the holder session.