Re: A Special Case of Library Cache Lock Wait Event.

On Mar 10, 1:15 am, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> On Mar 9, 5:59 am, "AnySQL" <any..._at_gmail.com> wrote:
>
>
>
>
>
> > On Oracle 10g, I got the following wait of two sessions, session 32
> > blocked session 29. It's interesting and dangerous operation in OLTP
> > system.
>
> > SQL> SELECT SID, EVENT FROM V$SESSION
> > 2 WHERE USERNAME='ANYSQL';
>
> > SID EVENT
> > ---------- -------------------------------------
> > 29 library cache lock
> > 32 SQL*Net message from client
>
> > What I executed in session 32 is :
>
> > SQL> exec dbms_mview.begin_table_reorganization('ANYSQL','T_IOT');
>
> > PL/SQL procedure successfully completed.
>
> > And what it's executing in session 29 is a delete statement, which
> > was blocked by library cache lock :
>
> > SQL> delete t_iot;
>
> > Seems some of Oracle new features are dangerous, however if I
> > submit a commit statement in session 32, then session 29 will continue
> > to execute, the library cache lock will go away. Why not Oracle issue
> > a default commit in this procedure?
>
> > From:http://www.anysql.net/en/oracle/mview_library_cache_lock.html
>
> > AnySQL.net
> > ==========================http://www.anysql.net/en
>
> Why are you deleting from an MVIEW? Materialized views exist to be
> read. I would expect that you should be deleting and updating the
> view base tables and then the refresh would update the mview.
>
> Also you should specifiy the full version since Oracle has made a
> couple of changes in how materialized views are updated that vary from
> 9.2 to 10.1 to 10.2. In one such change truncate was replaced with
> delete then I believe that the delete became a truncate again for one
> form of refresh.
>
> With the full version someone with significant experience with MVIEWS
> might be able to explain if what you are seeing is known behavior and
> classified as a bug or just how the feature works.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

I wait to maintain the master table of mview.

In this case, there is no mview log & mview for table T_IOT, and I do a sql_trace to this procedure, found nothing related.

AnySQL.net