sql - Bulk insert into partitioned table and table level lock -

i want know core reason(the mechanics of segments, blocks, locks engine does) why bulk insert(with direct-path) locks entire table if insert partition, can't truncate partition not affected(apparently) insert.

a conventional insert(without append hint) permits truncate nonaffected partitions.(notice speak non-commited transaction.)

below example ilustrate it.

let table:

 create table fg_test     (col number )    partition range (col)   (partition "p1"  values less (1000),    partition "p2"  values less (2000));  insert table fg_test values (1); insert table fg_test values (1000); commit; 

session 1:

insert table fg_test select * fg_test col >=1000; --1 rows inserted; 

session 2:

alter table fg_test truncate partition p1; --table truncated 

session 1:

rollback; insert /*+append */ table fg_test select * fg_test col >=1000; --1 rows inserted; 

session 2:

alter table fg_test truncate partition p1; --this throws ora-00054: resource busy , acquire nowait specified  --or timeout expired 

the doc on diret-path insert pretty abrupt on subject , says:

during direct-path insert, database obtains exclusive locks on table (or on partitions of partitioned table). result, users cannot perform concurrent insert, update, or delete operations on table, , concurrent index creation , build operations not permitted.

the how direct-path insert works not explain why lock needed partitions. , why conventional insert not lock nonaffected partitions? (my intuition lock done @ block level)

your premise wrong. direct-path insert not lock entire table if use partition extension clause.

session 1:

insert /*+append */ fg_test partition (p2) select * fg_test col >=1000; 

session 2:

alter table fg_test truncate partition p1; --table truncated 

the new question is: when partition extension clause not used, why conventional , direct-path inserts have different locking mechanisms? clarification makes question easier, without inside knowledge answer below still guess.

---

it easier code feature locks entire table. , runs faster, since there no need track partitions updated.

there's no need more fine-grained lock. systems or processes use direct-path writes update 1 large table @ time. if more fine-grained lock needed, partition extension clause can used. it's not quite convenient, since 1 partition can referenced @ time. it's enough 99.9% of time.