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

From: Ls Cheng <exriscer_at_gmail.com>
Date: Mon, 2 Mar 2015 22:14:57 +0100
Message-ID: <CAJ2-Qb-xdnAs1CWfb7b_p-j9jKP84_dmRuU8bfiG2zzv=Naodg_at_mail.gmail.com>

I wasnt expect using a table alias can actually get a DML statement blocked......

On Mon, Mar 2, 2015 at 6:12 PM, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> It's the parse that's the problem.
> I suppose we can understand why it's happening but it's easy to argue why
> it shouldn't be necessary as "z" is resolvable locally within the SQL
> statement.
> Seems to be same in 11.2.0.3 and 12.1.0.2
>
> Sent from my iPhone
>
> > On 2 Mar 2015, at 16:13, "Ls Cheng" <exriscer_at_gmail.com> wrote:
> >
> > 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:14:57 CET