Direct Path Insert into a Partitioned Table

 Normally a Direct Path Insert that is not committed blocks other concurrent Direct Path Inserts.  This behaviour also extends to Partitioned Tables.

UPDATE : Correction to 3 lines explaining how the two sessions see rows after commits.

Thus :

--------- From Session 1 : inserting only into the first Partition p_100
SQL> l
  1  create table my_part_table (id_col number, data_col varchar2(25))
  2  partition by range (id_col)
  3  (partition p_100 values less than (101),
  4   partition p_200 values less than (201),
  5   partition p_300 values less than (301)
  6* )
SQL>
SQL> /

Table created.

SQL>
SQL> l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>

---------- From Session 2 :  where we know that the rows will actually be inserted into a different Partition p_200
SQL> l
  1  insert /*+ APPEND */ into my_part_table
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

-- Yet, Session 2 is blocked and has to wait untill Session 1 does a COMMIT or ROLLBACK

Even though the 2 sessions will be inserting into separate Partitions (i.e. separate Segments). the first session blocks  the other Direct Path Insert.

However, this blocking can be avoided by explicitly naming the target Partition

Thus :

--------- From Session 1 : inserting only into the first Partition p_100 explicitly named 
SQL> l
  1  insert /*+ APPEND */ into my_part_table partition (p_100)
  2  select rownum, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
---------- From Session 2 :  inserting into the second Parition p_200 explicitlly named
SQL> l
  1  insert /*+ APPEND */ into my_part_table partition (p_200)
  2  select rownum+101, dbms_random.string('X',12)
  3  from dual
  4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
-------- Of course, both sessions can't requery until they COMMIT or ROLLBACK
--- note : Session 2 can query and see it's own rows after it does a COMMIT
---        Similarly, Session 1 can query and see it's own rows after it does a COMMIT
---        And they can see all the rows after both do a COMMIT
------------------------- the above 3 lines are correction to this blog post 
SQL> select count(*) from my_part_table;
select count(*) from my_part_table
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> 
SQL> commit; -- issued by both sessions so as to be able to see their own rows as well

Commit complete.

SQL>
SQL> select count(*) from my_part_table;

  COUNT(*)
----------
       100

SQL>
SQL> select count(*) from my_part_table partition (p_100);

  COUNT(*)
----------
        50

SQL>  select count(*) from my_part_table partition (p_200);

  COUNT(*)
----------
        50

SQL>

Thus, although there is general advice not to explicitly name a target Partition, I find this method useful if I have multiple concurrent Direct Path Inserts.