Hi Tom,

Thanks for the quick response but what is the reason behind this lock...why cannot we do before commit or rollback since we are able to do all the stuffs in serial insert?

Direct path inserts work different from the conventional path insert(serial insert). conventional path insert tries to reuse the free blocks (if any) with the High water (HW)mark and advances the HW to allocate new set of free blocks only if no free blocks are available.

while Direct path inserts always operates above the high water mark, by allocating new set of temporary blocks and add those temporary blocks to segments (and advance HWM)if transaction commit, in case of rollback it just discards them while retaining the HWM as such before the transaction.

In case of serial insert, Oracle will place row level lock, but in case of direct path insert it is a Exclusive Table lock.

rajesh@ORA11G> create table t(x int);

Table created.

rajesh@ORA11G> column object_name format a10
rajesh@ORA11G> select t2.object_name,locked_mode
  2  from v$locked_object t1,
  3      user_objects t2
  4  where t1.object_id = t2.object_id    ;

no rows selected

rajesh@ORA11G> insert into t select rownum from all_users;

38 rows created.

rajesh@ORA11G> select t2.object_name,locked_mode
  2  from v$locked_object t1,
  3      user_objects t2
  4  where t1.object_id = t2.object_id    ;

OBJECT_NAM LOCKED_MODE
---------- -----------
T                    3

1 row selected.

rajesh@ORA11G> commit;

Commit complete.

rajesh@ORA11G> insert /*+ append */  into t select rownum from all_user

38 rows created.

rajesh@ORA11G> select t2.object_name,locked_mode
  2  from v$locked_object t1,
  3      user_objects t2
  4  where t1.object_id = t2.object_id  ;

OBJECT_NAM LOCKED_MODE
---------- -----------
T                    6

1 row selected.

rajesh@ORA11G> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


rajesh@ORA11G> commit;

Commit complete.

rajesh@ORA11G> select count(*) from t;

  COUNT(*)
----------
        76

1 row selected.

rajesh@ORA11G>