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:
- 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.customersis 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_pdbin 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 containersfor 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.
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:
Start the discussion at forums.toadworld.com