merge waits on library cache lock when reference a table alias

From: Ls Cheng <exriscer_at_gmail.com>
Date: Mon, 2 Mar 2015 17:11:41 +0100
Message-ID: <CAJ2-Qb-JKxZZOJ4WkeuPVu5AqNCEvdwsJbWit+vT4vJtQEp6VQ_at_mail.gmail.com>



Hi

I hit a problem recently in 11.2.0.4 where a merge statement references a table alias, for example Z, and Z is being created as a tabe in another session using CTAS. The merge session hangs waiting for library cache lock.

I am not aware of such behaviour, anyone faced this before? Or it's time to file a SR?

Thanks

Test Case:

CREATE TABLE T1
AS
SELECT * from dba_users;

  • session 1 CREATE TABLE Z AS SELECT * FROM (SELECT rownum rn, 'A|B|C' ABC FROM dba_source) CONNECT BY LEVEL <= length(regexp_replace (ABC, '[^\|]+')) + 1;
  • session 2 hangs, library cache lock MERGE INTO t1 x USING (SELECT * FROM DBA_USERS ) z ON (x.user_id = z.user_id) WHEN MATCHED THEN UPDATE SET x.username = z.username WHEN NOT MATCHED THEN INSERT (username, user_id, password, account_status, lock_date, expiry_date, default_tablespace, temporary_tablespace, created, profile, initial_rsrc_consumer_group, external_name, password_versions, editions_enabled, authentication_type ) VALUES (z.username, z.user_id, z.password, z.account_status, z.lock_date, z.expiry_date, z.default_tablespace, z.temporary_tablespace, z.created, z.profile, z.initial_rsrc_consumer_group, z.external_name, z.password_versions, z.editions_enabled, z.authentication_type );
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 02 2015 - 17:11:41 CET

Original text of this message