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?