RE: merge waits on library cache lock when reference a table alias
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Mar 2015 21:36:47 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Ls Cheng [exriscer@xxxxxxxxx]
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
);
Other related posts:
