OS Environment: Oracle Linux 9.6 (64bit)
DB Environment: Oracle AI Database 23.26.3.0.0 ai

---

Starting with Oracle Database 26ai, a table can be queried or modified again within the same transaction after a Direct Path Insert.

In Oracle 19c, when a Direct Path Insert was performed using the APPEND hint, accessing the same table again before a COMMIT or ROLLBACK resulted in an ORA-12838 error.

In 26ai, this restriction is removed by default.

The behavior is controlled by the hidden parameter _online_direct_load. Setting this parameter to 0 restores the behavior of previous versions, where the same table cannot be accessed again after an append insert until the transaction is completed.

Reference: Oracle 26ai – Direct Load (Direct Path Insert) Transaction Restriction Removed

In addition to the _online_direct_load hidden parameter, the NO_MULTI_APPEND hint tested in this article can also restore the previous behavior.

When APPEND and NO_MULTI_APPEND are used together, Oracle 26ai raises ORA-12838 when the same table is queried or modified again after a Direct Path Insert, similar to Oracle 19c.

Tests

1. Check the MULTI_APPEND hint
2. Check the test environment
3. Create a sample table
4. Test default APPEND behavior
5. Test APPEND + MULTI_APPEND
6. Test APPEND + NO_MULTI_APPEND
7. Compare with NOAPPEND
8. Compare the results

---

1. Check the MULTI_APPEND Hint

First, check whether the hints exist in V$SQL_HINT.

SQL>
set lines 200 pages 1000
col name for a30
col inverse for a30
col version for a15
select name, inverse, target_level, version
from v$sql_hint
where name in ('APPEND', 'NOAPPEND',
'MULTI_APPEND', 'NO_MULTI_APPEND')
order by name;

NAME                           INVERSE                        TARGET_LEVEL VERSION
------------------------------ ------------------------------ ------------ ---------------
APPEND                         NOAPPEND                                  1 8.1.0
MULTI_APPEND                   NO_MULTI_APPEND                           1 23.1.0
NOAPPEND                       APPEND                                    1 8.1.0
NO_MULTI_APPEND                MULTI_APPEND                              1 23.1.0

MULTI_APPEND and NO_MULTI_APPEND are available and show a version of 23.1.0.

---

2. Check the Test Environment

Check the database version.

SQL>
set lines 200 pages 1000
select * from v$version;

BANNER
---------------------------------------------------------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY                                                                                                                         CON_ID
--------------------------------------------------------------------------------------------------------------------------------- ----------
Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production
Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production
Version 23.26.3.0.0
Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production                                                                0

The database version used for this test is 23.26.3.0.0.

Check the COMPATIBLE parameter.

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      23.6.0
noncdb_compatible                    boolean     FALSE

The COMPATIBLE parameter is set to 23.6.0.

---

3. Create a Sample Table

Create EMP2 from the EMP table.

SQL>
drop table emp2 purge;
create table emp2 as select * from emp;

Check the number of rows.

SQL>
select count(*) from emp2;

  COUNT(*)
----------
        14

The table currently contains 14 rows.

---

4. Test Default APPEND Behavior

Set STATISTICS_LEVEL to ALL so the execution plan can be checked.

SQL> alter session set statistics_level = all;

Session altered.

First, use only the existing APPEND hint without explicitly specifying MULTI_APPEND.

SQL> insert /*+ append */ into emp2 select * from emp2;

14 rows created.

14 rows are inserted, bringing the total to 28 rows.

Before issuing a COMMIT, check the execution plan.

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');

Plan hash value: 1283500778

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      |      1 |      0 |00:00:00.01 |      13 |      1 |       |       |          |
|   1 |  LOAD AS SELECT                  | EMP2 |      1 |      0 |00:00:00.01 |      13 |      1 |  1043K|  1043K| 1043K (0)|
|   2 |   OPTIMIZER STATISTICS GATHERING |      |      1 |     14 |00:00:00.01 |       3 |      0 |   256K|   256K|          |
|   3 |    TABLE ACCESS FULL             | EMP2 |      1 |     14 |00:00:00.01 |       2 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
      LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
      FULL(@"INS$1" "EMP2"@"INS$1")
      FULL(@"SEL$1" "EMP2"@"SEL$1")
      END_OUTLINE_DATA
  */


33 rows selected.

The execution plan shows LOAD AS SELECT, and the outline contains LOAD_METHOD(... HIGH_WATER_MARK), confirming that the APPEND hint was applied and a Direct Path Insert was used.

Now query EMP2 without committing the transaction.

SQL> select count(*) from emp2;

  COUNT(*)
----------
        28

All 28 rows can be queried successfully.

In Oracle 19c, this is where ORA-12838 would normally occur. In 26ai, however, the table can be queried without ending the transaction.

Reference: Oracle 26ai – Direct Load (Direct Path Insert) Transaction Restriction Removed

Run another APPEND insert without committing.

SQL> insert /*+ append */ into emp2 select * from emp2;

28 rows created.

Another 28 rows are inserted successfully.

Check the row count again.

SQL> select count(*) from emp2;

  COUNT(*)
----------
        56

The table now contains 56 rows.

This confirms that in Oracle 26ai, when only the APPEND hint is used, a table can be queried and another Direct Path Insert can be performed within the same transaction.

Rollback the test data.

SQL> rollback;

Rollback complete.

---

5. Test APPEND + MULTI_APPEND

Set STATISTICS_LEVEL to ALL again.

SQL> alter session set statistics_level = all;

Session altered.

This time, use APPEND together with MULTI_APPEND.

SQL> insert /*+ append multi_append */ into emp2 select * from emp2;

14 rows created.

Check the execution plan before committing.

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');

Plan hash value: 1283500778

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      |      1 |      0 |00:00:00.01 |      45 |      1 |       |       |          |
|   1 |  LOAD AS SELECT                  | EMP2 |      1 |      0 |00:00:00.01 |      45 |      1 |  1043K|  1043K| 1043K (0)|
|   2 |   OPTIMIZER STATISTICS GATHERING |      |      1 |     14 |00:00:00.01 |       8 |      0 |   256K|   256K|          |
|   3 |    TABLE ACCESS FULL             | EMP2 |      1 |     14 |00:00:00.01 |       7 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
      LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
      FULL(@"INS$1" "EMP2"@"INS$1")
      FULL(@"SEL$1" "EMP2"@"SEL$1")
      END_OUTLINE_DATA
  */


33 rows selected.

As with the previous test, the execution plan shows LOAD AS SELECT, and the outline contains LOAD_METHOD(... HIGH_WATER_MARK).

The LOAD_METHOD and LOAD_TYPE values are also the same as when only the APPEND hint was used.

Query EMP2 without committing.

SQL> select count(*) from emp2;

  COUNT(*)
----------
        28

The query succeeds and returns 28 rows.

In Oracle 19c, this operation would result in ORA-12838, but it works normally in 26ai.

Run another APPEND + MULTI_APPEND insert without committing.

SQL> insert /*+ append multi_append */ into emp2 select * from emp2;

28 rows created.

The insert succeeds.

Check the row count again.

SQL> select count(*) from emp2;

  COUNT(*)
----------
        56

The table now contains 56 rows.

The behavior is the same as using APPEND alone. With APPEND + MULTI_APPEND, Oracle 26ai allows the same table to be queried and another Direct Path Insert to be performed within the same transaction.

Rollback the test data.

SQL> rollback;

Rollback complete.

---

6. Test APPEND + NO_MULTI_APPEND

Set STATISTICS_LEVEL to ALL.

SQL> alter session set statistics_level = all;

Session altered.

This time, use APPEND together with NO_MULTI_APPEND.

SQL> insert /*+ append no_multi_append */ into emp2 select * from emp2;

14 rows created.

Before committing, check the execution plan.

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');

Plan hash value: 1283500778

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      |      1 |      0 |00:00:00.01 |      13 |      1 |       |       |          |
|   1 |  LOAD AS SELECT                  | EMP2 |      1 |      0 |00:00:00.01 |      13 |      1 |  1043K|  1043K| 1043K (0)|
|   2 |   OPTIMIZER STATISTICS GATHERING |      |      1 |     14 |00:00:00.01 |       8 |      0 |   256K|   256K|          |
|   3 |    TABLE ACCESS FULL             | EMP2 |      1 |     14 |00:00:00.01 |       7 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
      LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
      FULL(@"INS$1" "EMP2"@"INS$1")
      FULL(@"SEL$1" "EMP2"@"SEL$1")
      END_OUTLINE_DATA
  */


33 rows selected.

The execution plan still shows LOAD AS SELECT, and the outline contains LOAD_METHOD(... HIGH_WATER_MARK).

This means that NO_MULTI_APPEND does not disable Direct Path Insert. The LOAD_METHOD and LOAD_TYPE values are the same as in the previous APPEND tests.

Now try to query EMP2 without committing.

SQL> select count(*) from emp2;
select count(*) from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Help: https://docs.oracle.com/error-help/db/ora-12838/

This time, ORA-12838 occurs, which is the same behavior seen in Oracle 19c.

Try another Direct Path Insert without committing.

SQL> insert /*+ append no_multi_append */ into emp2 select * from emp2;
insert /*+ append no_multi_append */ into emp2 select * from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Help: https://docs.oracle.com/error-help/db/ora-12838/

Again, ORA-12838 occurs.

Therefore, when APPEND and NO_MULTI_APPEND are used together in Oracle 26ai, the Direct Path Insert itself still occurs, but the same table cannot be queried or modified again within the same transaction.

This is effectively the same transaction restriction seen in Oracle 19c.

Rollback the test data.

SQL> rollback;

Rollback complete.

---

7. Compare with NOAPPEND

Next, compare NO_MULTI_APPEND with NOAPPEND.

First, perform the insert using only the NOAPPEND hint.

SQL> insert /*+ noappend */ into emp2 select * from emp2;

Check the execution plan before committing.

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');

Plan hash value: 2941272003

----------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |      1 |      0 |00:00:00.01 |      37 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP2 |      1 |      0 |00:00:00.01 |      37 |
|   2 |   TABLE ACCESS FULL      | EMP2 |      1 |     14 |00:00:00.01 |       7 |
----------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
      LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
      FULL(@"INS$1" "EMP2"@"INS$1")
      FULL(@"SEL$1" "EMP2"@"SEL$1")
      END_OUTLINE_DATA
  */


32 rows selected.

This execution plan shows LOAD TABLE CONVENTIONAL.

The outline also shows:

LOAD_METHOD(... CONVENTIONAL)

This confirms that NOAPPEND disables Direct Path Insert and uses a conventional insert instead.

In this case, the table can be queried without committing.

SQL> select count(*) from emp2;

  COUNT(*)
----------
        28

Another insert also works normally.

SQL> insert into emp2 select * from emp2;

28 rows created.

Rollback the test data.

SQL> rollback;

Rollback complete.

---

8. Compare the Results

The behavior observed in the tests can be summarized as follows:

Hint	Insert Method	Access Same Table Before COMMIT	Result
APPEND	Direct Path Insert	Yes	Works
APPEND MULTI_APPEND	Direct Path Insert	Yes	Works
APPEND NO_MULTI_APPEND	Direct Path Insert	No	ORA-12838
NOAPPEND	Conventional Insert	Yes	Works

NOAPPEND

• Opposite of the APPEND hint
• Does not use Direct Path Insert
• Uses a conventional insert
• The same table can be queried within the same transaction

NO_MULTI_APPEND

• Can be used together with APPEND
• Direct Path Insert is still used
• The execution plan shows LOAD AS SELECT
• Querying or modifying the same table after the Direct Path Insert results in ORA-12838

MULTI_APPEND

• Can be used together with APPEND
• Direct Path Insert is still used
• The execution plan shows LOAD AS SELECT
• The same table can be queried or modified again after the Direct Path Insert without an error
• This is also the default behavior in Oracle 26ai when only APPEND is specified

---

Conclusion

In Oracle 26ai, after performing a Direct Path Insert with the APPEND hint, the same table can be queried or another Direct Path Insert can be performed without ending the transaction.

Using APPEND alone and explicitly specifying APPEND + MULTI_APPEND produced the same behavior in this test.

On the other hand, when APPEND + NO_MULTI_APPEND was used, the Direct Path Insert itself still occurred, but accessing the same table afterward resulted in ORA-12838, just as it would in Oracle 19c.

The important point is that NO_MULTI_APPEND does not change an APPEND insert into a conventional insert.

Instead, it appears to restore the transaction restriction that was removed for Direct Path Inserts in 26ai.

In that sense, NO_MULTI_APPEND can be thought of as a hint-level way to get behavior similar to disabling the newer online direct load behavior controlled by the _online_direct_load hidden parameter.

---

References

• Oracle 26ai – Direct Load (Direct Path Insert) Transaction Restriction Removed
• Oracle 26ai – LOAD_METHOD and LOAD_TYPE Hints
• Oracle 26ai – Parallel DML Transaction Restriction Removed
• Oracle 23c Free – Unrestricted Direct Path Inserts
• Tuna DBA Blog

This post is based on my personal experiences and opinions.
It does not represent the official position or views of my current or former employers.
If you have any questions, feel free to leave a comment or send me an email.
Thank you.