RE: merge waits on library cache lock when reference a table alias

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 2 Mar 2015 21:36:47 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282742F9_at_EXMBX01.thus.corp>

It looks as if it's the INSERT bit that runs into trouble - if you have only the when matched update option then the statement completes. (I think I'd raise an SR, it looks like a bug.)

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Ls Cheng [exriscer_at_gmail.com] Sent: 02 March 2015 16:11
To: Oracle Mailinglist
Subject: merge waits on library cache lock when reference a table alias

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 - 22:36:47 CET