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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 3 Mar 2015 07:33:30 -0500
Message-ID: <5b3801d055ae$427f64e0$c77e2ea0$_at_rsiz.com>

er, sorry, … with session 2 starting before session 1 COMPLETES? (not begins).  

And also that you have no hang using a similar scenario if the table alias in session 2 for the select from dba_users is, say, z1, instead of z?  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Tuesday, March 03, 2015 7:27 AM
To: exriscer_at_gmail.com; 'Oracle Mailinglist' Subject: RE: merge waits on library cache lock when reference a table alias  

Just to verify, is it correct that the first CTAS of T1 is certainly complete as a preamble before the session1 and session 2 are started with session 2 starting before session 1 begins?  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ls Cheng Sent: Monday, March 02, 2015 11:12 AM
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 Tue Mar 03 2015 - 13:33:30 CET