CTAS of same object name in different schema is causing Library Cache Locks On Database (Doc ID 2880524.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

CTAS is causing Library Cache Locks on Database


After 19c upgrade, running the following CREATE TABLE statement is causing "library cache lock" on SQL's like SELECTS that is referencing  <SCHEMA2.OBJ_NAME> table.


CREATE TABLE <SCHEMA1.OBJ_NAME>       --> OBJ_NAME is same
AS
SELECT /*+ FULL(i) */ *
FROM <SCHEMA2.OBJ_NAME> i
WHERE <COL1> = 'XXXX'
UNION
SELECT /*+ FULL(h) */ *
FROM <SCHEMA2.OBJ_NAME> h               -->OBJ_NAME is same
WHERE <COL1> = 'XXXX'
UNION

ERROR
-----------------------
ORA-04021
.

SELECT statements from other concurrent sessions that is referencing SCHEMA2.OBJ_NAME through PUBLIC SYNONYM OBJ_NAME is waiting on "library cache lock" waits when above CTAS is executing.

Changes

 upgrade to 19c

Cause

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