Overview
In my last article, Oracle Multi-tenant Application Containers – Part IV, we learned about cross-container aggregation to query user-created data in a metadata-linked table across various application PDBs from one single place; i.e., the application root, without connecting to PDBs individually or creating corresponding database links.
In this article, I will discuss the cross-container DML statement, which is a convenient way for the Application Root administrator to perform DML operations on a table in any application PDB within the application container, without connecting to it or creating a corresponding database link. A common user connected to the application root can include the CONTAINERS clause to specify the target table for a DML operation in the DML statement. The target container (application PDB) in which the specified table needs to be modified can be specified:
- In the DML statement
- In an INSERT VALUES statement by specifying a value for CON_ID in the VALUES clause
- In an UPDATE or DELETE statement by specifying a CON_ID predicate in the WHERE clause.
- In the database property CONTAINERS_DEFAULT_TARGET in the application root: This defines the default target container for DML operations. When it is not set, the default target container for DML operations is the application root.
Now, I will demonstrate the cross-container DML statement in the following scenarios:
I. Target container specified in cross-container DML statement
- Insert statement
- Update statement
- Delete statement
II. Default target container for DML operations specified in CONTAINERS_DEFAULT_TARGET
- Cross container DML statement does not specify a value for the CON_ID
- Cross container DML statement specifies a value for the CON_ID
III. Default target container for DML operations not specified in CONTAINERS_DEFAULT_TARGET and the cross-container DML statement does not specify a value for the CON_ID
Current Scenario
Here, we have an Oracle database 12.2.0.1 CDB called orclcdb as shown. Within this CDB, we have created an application container sales_app for the sales application of an organization. Also we have four application PDBs, north_app_pdb, south_app_pdb, east_app_pdb, and west_app_pdb, which are databases supporting the sales_app application for various regional offices of the organization. While sharing the structure of the metadata-linked customers table stored in the application root sales_app_root, each application PDB can store region specific customer data in the table.
Demonstration
Let us first connect to the CDB orclcdb and verify that there is one application container root sales_app_root in this CDB.
SQL> conn sys/oracle@orclcdb as sysdba
set sqlprompt CDB$ROOT>
sho con_name
CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT>SELECT con_id, name, open_mode, application_root app_root,
application_pdb app_pdb, application_seed app_seed
from v$containers
where application_root = 'YES' and application_pdb = 'NO';
CON_ID NAME OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- -------------------- ---------- ---------- ---------- ----------
15 SALES_APP_ROOT READ WRITE YES NO NO
Connect to the application root sales_app_rootand note that there are four application PDBs (north_app_pdb, east_app_pdb, west_app_pdb, south_app_pdb) ) associated with it.
SQL> conn sys/oracle@host01:1522/sales_app_root as sysdba
set sqlprompt SALES_APP_ROOT>
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
---------- -------------------- ---------- -------- -------- --------
4 SOUTH_APP_PDB READ WRITE NO YES NO
5 EAST_APP_PDB READ WRITE NO YES NO
6 WEST_APP_PDB READ WRITE NO YES NO
7 NORTH_APP_PDB READ WRITE NO YES NO
15 SALES_APP_ROOT READ WRITE YES NO NO
5 rows selected.
Besides an implicit application, another application sales_app is currently installed in this container. All the application PDBs are synced with application sales_app version 1.0.
SALES_APP_ROOT>@get_app_status
APP_NAME APP_VERSION APP_ID APP_STATUS IMPLICIT
------------------------------------ ------------ ------ ------------ --------
APP$5DED1EE7F9C418C7E05364C909C0F9BD 1.0 2 NORMAL Y
SALES_APP 1.0 21 NORMAL N
SALES_APP_ROOT>@get_sales_app_pdb_status
NAME APP_NAME APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
SOUTH_APP_PDB SALES_APP 1.0 NORMAL
EAST_APP_PDB SALES_APP 1.0 NORMAL
WEST_APP_PDB SALES_APP 1.0 NORMAL
NORTH_APP_PDB SALES_APP 1.0 NORMAL
Let us check the common objects currently in the application sales_app. There is a metadata-linked common application table sales_app_user.customers with the structure as shown.
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 NOT NULL NUMBER
CUST_NAME VARCHAR2(30)
CUST_ADD VARCHAR2(30)
CUST_ZIP NUMBER
Currently there are not any customer records in the metadata-linked table customers in any application PDB.
SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
cust.CUST_ZIP, con.name con_name
from containers(sales_app_user.customers) cust,
v$containers con
where cust.con_id = con.con_id;
no rows selected
Now, I will use a cross-container DML to insert application PDB-specific customer records into the metadata -linked table customers while connected to application root sales_app_root.
Let us first issue a query to find the CON_ID’s of the various application PDBs. We will need this information to issue the cross-container DML.
SALES_APP_ROOT>select con_id, name, application_pdb
from v$containers
where application_pdb = 'YES'
and application_seed = 'NO';
CON_ID NAME APP
---------- -------------------- ---
4 SOUTH_APP_PDB YES
5 EAST_APP_PDB YES
6 WEST_APP_PDB YES
7 NORTH_APP_PDB YES
I. Target container specified in cross-container DML statement
The DML statement uses
- A CONTAINERS clause to specify the target table for DML operation and
- CON_ID to specify the target container (application PDB)
Insert statement
While connected to application root sales_app_root, we will add one customer record to each of the application PDBs by executing cross-container insert statements. Note the use of
- The CONTAINERS clause to specify the target table for insert operation
- CON_ID in the VALUES clause to specify the target container
SALES_APP_ROOT>insert into containers(sales_app_user.customers)
(con_id,cust_id,cust_name,cust_add, cust_zip) values
(7, '1', 'Cust1(North)','USA(North) address', 24);
1 row created.
SALES_APP_ROOT>insert into containers(sales_app_user.customers)
(con_id,cust_id,cust_name,cust_add, cust_zip) values
(4, '1', 'Cust1(South)','USA(South) address', 25);
1 row created.
SALES_APP_ROOT>insert into containers(sales_app_user.customers)
(con_id,cust_id,cust_name,cust_add, cust_zip) values
(6, '1', 'Cust1(West)','USA(West) address', 26);
1 row created.
SALES_APP_ROOT>insert into containers(sales_app_user.customers)
(con_id,cust_id,cust_name,cust_add, cust_zip) values
(5, '1', 'Cust1(East)','USA(East) address', 28);
1 row created.
Let us verify if the above rows have been successfully inserted.
SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
cust.CUST_ZIP, con.name con_name
from containers(sales_app_user.customers) cust,
v$containers con
where cust.con_id = con.con_id;
no rows selected
It is surprising that despite the message for successful inserts, the records are not visible. The reason is that cross container DML operations need to be completed by a commit so that changes are visible to subsequent queries. Let us commit and verify the addition of the inserted records.
SALES_APP_ROOT>commit;
Commit complete.
SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
cust.CUST_ZIP, cust.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_ZIP CON_ID CON_NAME
-------- --------------- -------------------- -------- ------ --------------
1 Cust1(South) USA(South) address 25 4 SOUTH_APP_PDB
1 Cust1(East) USA(East) address 28 5 EAST_APP_PDB
1 Cust1(West) USA(West) address 26 6 WEST_APP_PDB
1 Cust1(North) USA(North) address 24 7 NORTH_APP_PDB
We can also verify that the customers added are visible within the corresponding application PDBs as well.
EAST_APP_PDB>select * from sales_app_user.customers;
CUST_ID CUST_NAME CUST_ADD CUST_ZIP
---------- --------------- ------------------------------ ----------
1 Cust1(East) USA(East) address 28
WEST_APP_PDB>select * from sales_app_user.customers;
CUST_ID CUST_NAME CUST_ADD CUST_ZIP
---------- --------------- ------------------------------ ----------
1 Cust1(West) USA(West) address 26
NORTH_APP_PDB>select * from sales_app_user.customers;
CUST_ID CUST_NAME CUST_ADD CUST_ZIP
---------- --------------- ------------------------------ ----------
1 Cust1(North) USA(North) address 24
SOUTH_APP_PDB>select * from sales_app_user.customers;
CUST_ID CUST_NAME CUST_ADD CUST_ZIP
---------- --------------- ------------------------------ ----------
1 Cust1(South) USA(South) address 25
Update Statement
While connected to the application root, We can also update rows in a table in an application PDB within the container by specifying the target container by means of a CON_ID predicate in the WHERE clause.
Let us update the record for the customer with CUST_ID = 1 in west_app_pdb (con_id = 6).Note that here also a commit is required for the update to be visible.
SALES_APP_ROOT>UPDATE CONTAINERS(sales_app_user.customers)
SET cust_add ='New USA(West) address '
WHERE CON_ID = 6 AND CUST_ID=1;
1 row updated.
SALES_APP_ROOT>commit;
Commit complete.
WEST_APP_PDB>select * from sales_app_user.customers;
CUST_ID CUST_NAME CUST_ADD CUST_ZIP
---------- --------------- ------------------------------ ----------
1 Cust1(West) New USA(West) address 26
Delete statement
Similarly, while connected to the application root, we can also delete selected records from a specified table in an application PDB within the container by specifying the target container by means of a CON_ID predicate in the WHERE clause. Let us delete the only customer record from west_app_pdb (con_id = 6)
SALES_APP_ROOT>DELETE FROM CONTAINERS(sales_app_user.customers)
WHERE CON_ID = 6
AND CUST_ID=1;
COMMIT;
WEST_APP_PDB>select * from sales_app_user.customers
no rows selected
It is worth mentioning here that in cross-container update and delete statements, only one CON_ID can be specified in the WHERE clause; i.e., a cross-container DML can modify table rows in at most one application PDB.
II. Target container specified in CONTAINERS_DEFAULT_TARGET
Another method of specifying the target container of the a DML operation is by defining the default target container for DML operations by setting the database property CONTAINERS_DEFAULT_TARGET in the application root. Currently, this property is not set.
SALES_APP_ROOT>SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET';
no rows selected
Let us set the default target containers for DML operations to west_app_pdb
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (WEST_APP_PDB);
Note that the value of the database property CONTAINERS_DEFAULT_TARGET gets set to DBID of the application PDB west_app_pdb
SALES_APP_ROOT>SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET';
PROPERTY_VALUE
--------------------
3266022853
WEST_APP_PDB>select dbid from v$containers;
DBID
----------
3266022853
It is worth mentioning here that only one default container is allowed.
IIa) The cross-container DML statement does not specify a value for the CON_ID
Now that the database property CONTAINERS_DEFAULT_TARGET has been set to west_app_pdb, let us issue a cross-container insert statement without specifying a value for the CON_ID in it
SALES_APP_ROOT>insert into containers(sales_app_user.customers)
(CUST_ID, CUST_NAME, CUST_ADD, CUST_ZIP) values
('2', 'Cust2(West)', 'USA(West) address', 26);
commit;
It can be seen that the above record has been inserted into the current default container for DML operations; i.e., west_app_pdb.
WEST_APP_PDB>select * from sales_app_user.customers;
CUST_ID CUST_NAME CUST_ADD CUST_ZIP
---------- --------------- ------------------------------ ----------
2 Cust2(West) USA(West) address 26
Similarly, if an update statement does not specify values for the CON_ID in the WHERE clause, then the target container will be the one specified in the database property CONTAINERS_DEFAULT_TARGET in the application root.
Let us issue an update statement using the CONTAINERS clause but without a CON_ID in the WHERE clause.
It can be seen that only the record(s) in the current default container for DML operations (i.e., west_app_pdb) which satisfy the condition mentioned in the WHERE clause get updated.
SALES_APP_ROOT>UPDATE CONTAINERS(sales_app_user.customers)
SET cust_add ='New ' || cust_add
WHERE CUST_ID= 2;
1 row updated.
SALES_APP_ROOT>commit;
Commit complete.
CUST_ID CUST_NAME CUST_ADD CUST_ZIP CON_NAME
---------- -------------------- ------------------------- ---------- ---------
1 Cust1(South) USA(South) address 25 SOUTH_APP_PDB
1 Cust1(East) USA(East) address 28 EAST_APP_PDB
2 Cust2(West) New USA(West) address 26 WEST_APP_PDB
1 Cust1(North) USA(North) address 24 NORTH_APP_PDB
Let us issue a delete statement using the CONTAINERS clause but without a CON_ID in the WHERE clause
SALES_APP_ROOT>DELETE FROM CONTAINERS(sales_app_user.customers);
1 row deleted.
SALES_APP_ROOT>commit;
Commit complete.
Note that customer record has been deleted from the default container, i.e., west_app_pdb.
SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
cust.CUST_ZIP, 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_ZIP CON_NAME
---------- ----------- ------------------------- ---------- ---------------
1 Cust1(South) USA(South) address 25 SOUTH_APP_PDB
1 Cust1(East) USA(East) address 28 EAST_APP_PDB
1 Cust1(North) USA(North) address 24 NORTH_APP_PDB
Thus, if a cross-container DML statement does not specify values for the CON_ID in the WHERE clause, then the target container will be the one specified in the database property CONTAINERS_DEFAULT_TARGET in the application root.
IIb) Cross-container DML statement specifies a value for the CON_ID
Now, we will explore what happens when the database property CONTAINERS_DEFAULT_TARGET has been set and the cross-container DML statement also specifies a value for the CON_ID.
Let us set the default target container for DML operations to east_app_pdb (con_id = 5)
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (EAST_APP_PDB);
Now we will issue a cross-container insert statement specifying the CON_ID as 6 corresponding to application PDB west_app_pdb.
SALES_APP_ROOT>insert into containers(sales_app_user.customers)
(con_id,cust_id,cust_name,cust_add, cust_zip) values
(6, '3', 'Cust3','USA address', 26);
1 row created.
SALES_APP_ROOT>commit;
Commit complete.
Note that although east_app_pdb is the current default container for DML operations, a record has been inserted in application PDB west_app_pdb, whose CON_ID was specified in the cross-container insert statement above.
SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
cust.CUST_ZIP, 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_ZIP CON_NAME
---------- -------------------- ------------------------- ---------- ---------
1 Cust1(South) USA(South) address 25 SOUTH_APP_PDB
1 Cust1(East) USA(East) address 28 EAST_APP_PDB
3 Cust3 USA address 26 WEST_APP_PDB
1 Cust1(North) USA(North) address 24 NORTH_APP_PDB
Hence, when the database property CONTAINERS_DEFAULT_TARGET has been set and the cross-container DML statement also specifies a value for the CON_ID, the target container is the one specified in the DML statement.
III. Default target container for DML operations not specified in CONTAINERS_DEFAULT_TARGET and the cross-container DML statement does not specify a value for the CON_ID
To explore this case, we will clear the default container setting by issuing following statement.
SALES_APP_ROOT>ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = NONE;
Pluggable database altered.
SALES_APP_ROOT>SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
PROPERTY_NAME='CONTAINERS_DEFAULT_TARGET';
no rows selected
When it is not set, the default container is the application root. This can be verified by inserting a record into the customers table from the application root without specifying CON_ID in the cross-container DML statement.
SALES_APP_ROOT>insert into containers(sales_app_user.customers)
(cust_id,cust_name,cust_add, cust_zip) values
(1, 'Cust1(root)','USA(root) address', 23);
1 row created.
SALES_APP_ROOT>commit;
Commit complete.
As the database property CONTAINERS_DEFAULT_TARGET has not been set, the default container is the application root. As a result, the record has been inserted in the application root sales_app_root.
.SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
cust.CUST_ZIP, 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_ZIP CON_NAME
---------- -------------------- ------------------------- ---------- --------
1 Cust1(root) USA(root) address 23 SALES_APP_ROOT
1 Cust1(South) USA(South) address 25 SOUTH_APP_PDB
1 Cust1(East) USA(East) address 28 EAST_APP_PDB
1 Cust1(North) USA(North) address 24 NORTH_APP_PDB
Similarly, if we issue an update / delete statement without CON_ID in WHERE clause, the customer record in the application root gets updated / deleted.
When the default target container for DML operations is not specified in CONTAINERS_DEFAULT_TARGET and the cross-container DML statement also does not specify a value for the CON_ID, the cross-container DML statement modifies the table in the application root.
Summary
- Cross-container DML statement is a convenient way for the Application Root administrator to perform DML operations on a table in any application PDB within the application container, without connecting to it or creating a corresponding database link.
- A common user connected to the application root can include a CONTAINERS clause to specify the target table for DML operation in the DML statement.
- The target container (application PDB) in which the specified table needs to be modified can be specified
- In the DML statement by means of CON_ID
- By defining the default target container for DML operations by setting the database property CONTAINERS_DEFAULT_TARGET in the application root.
- Only one container can be specified as the default target container for DML operations.
- Cross-container DML operations need to be completed by a commit so that changes are visible to subsequent queries.
- A cross-container DML can modify table rows in at most one application PDB.
- When the database property CONTAINERS_DEFAULT_TARGET has been set in the application root and
- The cross-container DML statement also specifies a value for the CON_ID, the target container is the one specified in the DML statement.
- The cross-container DML statement does not specify a value for the CON_ID, the target container is the one specified in the database property CONTAINERS_DEFAULT_TARGET.
- When the default target container for DML operations is not specified in CONTAINERS_DEFAULT_TARGET and the cross-container DML statement also does not specify a value for the CON_ID, the cross-container DML statement modifies the table in the application root.
Note:
The following restrictions apply to the CONTAINERS clause:
- The CONTAINERS DEFAULT TARGET clause does not affect SELECT statements.
- INSERT as SELECT statements where the target of the INSERT is in CONTAINERS() is not supported.
- A multi-table INSERT statement where the target of the INSERT is in CONTAINERS() is not supported.
References:
https://docs.oracle.com/database/122/CNCPT/overview-of-the-multitenant-architecture.htm#CNCPT89304
Scripts used in this article:
get_app_containers.sql
— Find out the containers in an application root when executed from application root
SELECT con_id, name, open_mode, application_root app_root,
application_pdb app_pdb, application_seed app_seed
from v$containers
order by con_id;
get_app_status.sql
— Find out the status of various applications in an application container when executed from an application root
select app_name, app_version, app_id, app_status, app_implicit implicit from dba_applications;
get_sales_app_pdb_status
— Find out various versions of application SALES_APP and synchronization status of various application PDBs with it. Execute from application root.
SELECT c.name,
aps.app_name,
aps.app_version,
aps.app_status
FROM dba_app_pdb_status aps
JOIN v$containers c ON c.con_uid = aps.con_uid
WHERE aps.app_name = 'SALES_APP';
get_sales_app_objects
— Find out objects belonging to sales_app application.
select app.app_name, obj.owner, obj.object_name, obj.object_type,
obj.sharing, obj.application
from dba_objects obj, dba_applications app
where obj.owner in
(select username from dba_users
where oracle_maintained = 'N')
and obj.application = 'Y'
and obj.created_appid = app.app_id;
Start the discussion at forums.toadworld.com