Oracle Multiple Table Insert

We can insert the selected results into multiple tables using one query "insert all".

SQL> create table t2 (val number);

Table created.

SQL> create table t2 (val number);

Table created.

SQL> insert all into t1 into t2 select 1 from dual;

2 rows created.

SQL> select * from t1;

  VAL
-----
    1

SQL> select * from t2;

  VAL
-----
    1

However, multiple table insert does not work for remote tables. We can only insert data into one remote table at a time.

SQL> insert all into t1 into t3@DL_ANOTHER_DB select 1 from dual;
insert all into t3 into t2@DL_APEX select 1 from dual
                           *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

SQL> insert  into t2@DL_APEX select 1 from dual;

1 row created.