A Special Case of Library Cache Lock Wait Event.

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