Re: merge waits on library cache lock when reference a table alias
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Tue, 3 Mar 2015 18:03:17 +0100
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@xxxxxxxxxxxxx] *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@xxxxxxxxxxxxx <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:
