Introduction

Oracle documentation states that it is possible to update a metadata linked table in multiple Application PDBs in a single DML statement. Here are some excerpts with relevant parts highlighted:

1. https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-AA8C5BC8-4A22-4E8D-BA36-85B99A1168C2

  • In an application root, a single DML statement that includes the CONTAINERS clause can modify a table or view in one or more containers in the application container.
  • The following DML statement updates the sales.customers table in the containers with a CON_ID of 7 or 8:

UPDATE CONTAINERS(sales.customers) ctab

   SET ctab.city_name='MIAMI'

   WHERE ctab.CON_ID IN(7,8) AND

   CUSTOMER_ID=3425;

2. https://docs.oracle.com/database/122/CNCPT/overview-of-the-multitenant-architecture.htm#CNCPT89304

  • When you are connected to either the CDB root or an application root, you can execute a single DML statement to modify tables or views in multiple PDBs within the container.
  • In this example, your goal is to set the country_name column to the value USA in the sh.sales table. This table exists in two separate PDBs, with container IDs of 7 and 8. Both PDBs are in the application container named saas_sales_ac. You can connect to the application root as an administrator, and make the update as follows:

CONNECT sales_admin@saas_sales_ac

Password: *******

UPDATE CONTAINERS(sh.sales) sal

  SET sal.country_name = 'USA'

  WHERE sal.CON_ID IN (7,8);

Verification

In order to verify the above, I have created an application container sales_app_root for a sales application. The application container has four application PDBs; north_app_pdb, south_app_pdb, east_app_pdb, and west_app_pdb. The application consists of a common user sales_app_user and a metadata-linked table sales_app_user.customers.

SALES_APP_ROOT>sho con_name

 

CON_NAME

------------------------------

SALES_APP_ROOT

 

SALES_APP_ROOT>@get_app_containers

 

    CON_ID NAME                  OPEN_MODE  APP_ROOT APP_PDB  APP_SEED

---------- -------------------- ---------- -------- -------- --------

         3 SALES_APP_ROOT       READ WRITE YES       NO     NO

         4 NORTH_APP_PDB         READ WRITE NO        YES      NO

         5 SOUTH_APP_PDB        READ WRITE NO        YES      NO

         6 WEST_APP_PDB          READ WRITE NO        YES      NO

         8 EAST_APP_PDB          READ WRITE NO        YES      NO

         9 SALES_APP_ROOT$SEED   READ WRITE NO        YES      YES

 

6 rows selected.

 

SALES_APP_ROOT>@get_sales_app_objects

 

APP_NAME   OWNER          OBJECT_NAME  OBJECT_TYPE SHARING         APPLICATION

---------- --------------- ------------ ----------- ------------ ------------

SALES_APP  SALES_APP_USER  CUSTOMERS    TABLE        METADATA LINK      Y

 

SALES_APP_ROOT>desc sales_app_user.customers

 

 Name                     Null?   Type

 ----------------------- -------- ----------------

 CUST_ID                           VARCHAR2(10)

 CUST_NAME                         VARCHAR2(30)

 CUST_ADD                          VARCHAR2(30)

 CUST_ZONE                        CHAR(1)

 

Customer data in the table sales_app_user.customers is partitioned across application PDBs based on the column CUST_ZONE. Currently, we have four records in the table in each of the application PDBs, as shown.

SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,

                       cust.CUST_ZONE, con.con_id, con.name con_name

                from   containers(sales_app_user.customers) cust,

                       v$containers con

                where cust.con_id = con.con_id; 

 

CUST_ID CUST_NAME    CUST_ADD            CUST_ZONE  CON_ID CON_NAME

------- ------------ -------------------- --------- ------- ----------

E1    East_Cust_1  East_Cust_1_address  E           8 EAST_APP_PDB

E2    East_Cust_2  East_Cust_2_address  E           8 EAST_APP_PDB

E3    East_Cust_3  East_Cust_3_address  E           8 EAST_APP_PDB

E4    East_Cust_4  East_Cust_4_address  E           8 EAST_APP_PDB

W1    West_Cust_1  West_Cust_1_address  W           6 WEST_APP_PDB

W2    West_Cust_2  West_Cust_2_address  W           6 WEST_APP_PDB

W3    West_Cust_3  West_Cust_3_address  W           6 WEST_APP_PDB

W4    West_Cust_4  West_Cust_4_address  W           6 WEST_APP_PDB

N1    North_Cust_1 North_Cust_1_address N           4 NORTH_APP_PDB

N2    North_Cust_2 North_Cust_2_address N           4 NORTH_APP_PDB

N3    North_Cust_3 North_Cust_3_address N           4 NORTH_APP_PDB

N4    North_Cust_4 North_Cust_4_address N          4 NORTH_APP_PDB

S1    South_Cust_1 South_Cust_1_address S           5 SOUTH_APP_PDB

S2    South_Cust_2 South_Cust_2_address S           5 SOUTH_APP_PDB

S3    South_Cust_3 South_Cust_3_address S           5 SOUTH_APP_PDB

S4    South_Cust_4 South_Cust_4_address S           5 SOUTH_APP_PDB

 

16 rows selected.

 

Problem: Is there a way to update a table in all the application PDBs from the application root?

Let us try to update column CUST_ADD in the table sales_app_user.customers for the application PDBs east_app_pdb (con_id = 8) and west_app_pdb (con_id = 6) in a single DML. It can be seen that the DML fails with ORA-65319 and in order to resolve the error, I have been instructed to "Specify the CON_ID predicate within an AND chain and without an in-list"

SALES_APP_ROOT>UPDATE CONTAINERS(sales_app_user.customers) ctab

             SET ctab.cust_add ='New ' || cust_add

             WHERE CON_ID IN(6,8) ;

 

ERROR at line 1:

ORA-65319: DML on CONTAINERS() specified an unsupported CON_ID predicate

 

SALES_APP_ROOT>ho oerr ora 65319

 

65319, 00000, "DML on CONTAINERS() specified an unsupported CON_ID predicate"

// *Cause:  A DML on CONTAINERS() included the CON_ID predicate in an OR chain

//          or in an in-list.

// *Action: Specify the CON_ID predicate within an AND chain and without an

//          in-list.

//

 

However, if I specify single CON_ID for the application PDB west_app_pdb in the WHERE clause, the update succeeds.

SALES_APP_ROOT>UPDATE CONTAINERS(sales_app_user.customers) ctab

             SET ctab.cust_add ='New ' || cust_add

             WHERE CON_ID = 6;  

 

4 rows updated.

 

SALES_APP_ROOT>commit;

 

Commit complete.

 

SALES_APP_ROOT>select * from containers(sales_app_user.customers) where con_id = 6;

 

CUST_ID CUST_NAME          CUST_ADD                CUST_ZONE  CON_ID

------- -------------------- ------------------------- --------- -----

W1    West_Cust_1      New West_Cust_1_address   W        6

W2    West_Cust_2      New West_Cust_2_address   W        6

W3    West_Cust_3      New West_Cust_3_address   W        6

W4    West_Cust_4      New West_Cust_4_address   W        6

 

It seems that it is not possible to update a table in more than one application PDB using above method.

However, the Oracle documentation https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-AA8C5BC8-4A22-4E8D-BA36-85B99A1168C2 mentions another method of performing cross-container updates by specifying default target containers.

You can specify default target containers for DML operations. If a DML statement does not specify values for the CON_ID in the WHERE clause, then the target containers of the DML operation are those specified in the database property CONTAINERS_DEFAULT_TARGET in the application root. When issued in an application root, the following DML statement modifies the default target containers for the application container:

UPDATE CONTAINERS(sales.customers) ctab

   SET ctab.city_name='MIAMI'

   WHERE CUSTOMER_ID=3425;

In order to apply above method, let us first find out the current value of default target containers for our application container.

SALES_APP_ROOT>SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES

               WHERE PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET';

 

no rows selected

It can be seen that currently the default target containers are not set.

Let us try to set the default target containers to east_app_pdb and north_app_pdb as per the following paragraph in the Oracle documentation https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-AA8C5BC8-4A22-4E8D-BA36-85B99A1168C2:

To specify the default container for DML statements in an application container, issue the ALTER PLUGGABLE DATABASE statement with the CONTAINERS DEFAULT TARGET clause.

....

....

This example specifies that APDB1 is the default container for DML statements in the application container.

ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (APDB1);

It can be seen that various attempts to set default target containers to more than one application PDB fail.

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (EAST_APP_PDB,NORTH_APP_PDB);

ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (EAST_APP_PDB,NORTH_APP_PDB)

                                                                  *

ERROR at line 1:

ORA-02000: missing ) keyword

 

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = ('EAST_APP_PDB','NORTH_APP_PDB');

 

ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = ('EAST_APP_PDB','NORTH_APP_PDB')

                                                      *

ERROR at line 1:

ORA-65324: An invalid value is specified in an ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET or ALTER DATABASE CONTAINERS DEFAULT TARGET statement.

 

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (EAST_APP_PDB),(NORTH_APP_PDB);

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (EAST_APP_PDB),(NORTH_APP_PDB)

                                                                   *

ERROR at line 1:

ORA-00922: missing or invalid option

 

Scrolling further down in the Oracle documentation, the reason for failure becomes clear:

When a DML statement is issued in an application root without specifying containers in the WHERE clause, the DML statement affects the default container for the application container. The default container can be any container in the application container, including the application root or an application PDB. Only one default container is allowed.

The following link https://docs.oracle.com/database/122/SQLRF/ALTER-PLUGGABLE-DATABASE.htm#SQLRF55667 clarifies things further:

CONTAINERS DEFAULT TARGET

Use this clause to specify the default container for DML statements in an application container. You must be connect to the application root.

For container_name, specify the name of the default container. The default container can be any container in the application container, including the application root or an application PDB. You can specify only one default container.

Let us set the default target container to one application PDB only; i.e., east_app_pdb:

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (EAST_APP_PDB);

Pluggable database altered.

Finally, I have been able to set the default target container. Now let us check if an update statement affects the default target container east_app_pdb even if CON_ID is not specified in the WHERE clause.

SALES_APP_ROOT>UPDATE CONTAINERS(sales_app_user.customers) ctab

             SET ctab.cust_add ='New ' || cust_add;   

 

4 rows updated.

 

SALES_APP_ROOT>commit;

 

Commit complete.

 

SALES_APP_ROOT>select * from containers(sales_app_user.customers) where con_id = 8;

 

CUST_ID CUST_NAME     CUST_ADD                   CUST_ZONE CON_ID

------- -------------- ------------------------- --------- ------

E1    East_Cust_1 New East_Cust_1_address   E              8

E2    East_Cust_2 New East_Cust_2_address   E              8

E3    East_Cust_3 New East_Cust_3_address   E              8

E4    East_Cust_4 New East_Cust_4_address   E              8

 

Well, it works!

Conclusion

Hence, it seems that it is not possible to update a table in multiple Application PDBs in a single DML statement. In an application root, a single DML statement that includes the CONTAINERS clause can modify a table or view in at most one container only in the application container.

References: