My Oracle Support Banner

Concurrent DDL / select against MV base table results in Deadlock (Doc ID 282237.1)

Last updated on MARCH 02, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 to 10.1.0.5 [Release 9.2 to 10.1]
Information in this document applies to any platform.

Symptoms

Data Warehouse environment.

DDL operation being performed against partition of base table referenced by Materialized View(s).

query_rewrite_enabled = true

Query against the base table at the same time as partition operation results in :
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<The 2 sessions in deadlock indicate that current sql statement (refer to trace files) are :
1) ALTER SUMMARY "<SCHEMA-NAME>"."<TABLE-NAME>" COMPILE

Other salient details from trace file indicate :
waiting for 'library cache lock' blocking sess=0x0 seq=24740 wait_time=0
handle address=239a3ec8, lock address=22279738, 100*mode+namespace=c9
name=<SCHEMA-NAME>.<TABLE-NAME>
handle=239a3ec8 request=S

holding : "<TABLE-NAME>" row cache (dc_objects) entry
row cache enqueue: count=1 session=214b924c object=23fef804, mode=X

2) ALTER TABLE <SCHEMA-NAME>.<TABLE-NAME> DROP PARTITION <PARTITION-NAME>
Other salient details from trace file indicate :
waiting for :
row cache enqueue: count=1 session=214ba52c object=23fef804, request=X

holding : <SCHEMA-NAME>.<TABLE-NAME> library cache lock
LIBRARY OBJECT LOCK: lock=22260bc0 handle=239a3ec8 mode=X

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.