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

  • From: Ls Cheng <exriscer@xxxxxxxxx>
  • To: "Mark W. Farnham" <mwf@xxxxxxxx>
  • Date: Wed, 27 Jun 2018 19:47:25 +0200

Hi

This was 3 years ago, but someone asked me about this today, I thought I
would share the solution with all of you after all these time :-)

I raised a SR on March 2015 and I got the fix on 18th of Feb on 2016! It
was due to this bug

*Bug 14380605 - ORA-904/mutex contention from recursive 'SELECT 1 FROM DUAL
WHERE' done by "MERGE INTO"*

BR



<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Tue, Mar 3, 2015 at 6:03 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote:

Hi Mark

In session 1 run CTAS of T1 and Z, right afterwards run the merge
statement in session 2.

Table alias has to be same as the table being created.

Thanks


On Tue, Mar 3, 2015 at 1:33 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org] *On Behalf Of *Mark W. Farnham
*Sent:* Tuesday, March 03, 2015 7:27 AM
*To:* exriscer@xxxxxxxxx; '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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org <oracle-l-bounce@xxxxxxxxxxxxx>] *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
           );



Other related posts: