How to tell if "direct-load insert" was used with the APPEND hint

Both the serial and parallel version of:

   INSERT /*+ APPEND */
          INTO ...
    SELECT ...
        FROM ...

uses the same quick interface as Direct Load under some conditions. Conditions are listed in Concepts Guide.

How can one to tell if direct insert feature is used or not?



1) From the same session:
If after the INSERT /*+ APPEND */ ... statement but within the same
transaction we try to select from the table, the ORA-12838 error should return:

    insert /*+ APPEND */ into emp ;

select empno, ename, job, mgr, hiredate, sal, comm, deptno+50

from emp where deptno<50;

select count(*) from emp where rownum<10;

ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

2) The table locks are different.

If session A (session_id=10) executes an insert command:
insert into emp select empno, ename, job, mgr, hiredate, sal, comm, eptno+50 from emp where deptno<50;
       
Then from session B you can see:
select lock_type, mode_held from dba_locks where session_id=10;

    LOCK_TYPE             MODE_HELD
    --------------------- ---------------------
    Transaction           Exclusive
    DML                   Row-X (SX)

If session A (session_id=10) executes an insert command:
insert /*+ APPEND */ into emp select empno, ename, job, mgr, hiredate, sal, comm, deptno+50 from emp where deptno<50;

Then from session B you can see:
select lock_type, mode_held from dba_locks where session_id=10;

     LOCK_TYPE             MODE_HELD
     --------------------- ---------------------
     Transaction           Exclusive
     DML                   Exclusive

A select on v$transaction whith the a where clause on flag gives use also the information:
select se.username, se.sid, se.serial# from v$transaction tr, v$session se where se.taddr= tr.addr and bitand(tr.flag,power(2,24)) != 0;

      USERNAME         SID        SERIAL#
     ---------------- ---------- ----------
     SCOTT                    10          9

1. Any serial or parallel statements attempting to access a table that has already been modified by a direct-load INSERT (or parallel DML) within the same transaction are rejected with an error message.
2. Locking Considerations In direct-load INSERT, exclusive locks are obtained on the table (or on all the partitions of a partitioned table) precluding any concurrent insert, update, or delete on the table. Concurrent queries, however, are supported and will see only the data in the table before the INSERT began. These locks also prevent any concurrent index creation or rebuild operations. This must be taken into account before using direct-load INSERT because it affects table concurrency.