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

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 -- Received on Fri Mar 09 2007 - 11:15:46 CST