Toad World Blog

Oracle Multi-tenant Application Containers Part-V Cross Container DML

Nov 20, 2017 9:26:13 AM by Anju Garg

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_pdbsouth_app_pdbeast_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_root and 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/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-AAF93A02-7C70-4024-8758-E351C213543E

https://docs.oracle.com/database/122/ADMIN/administering-a-cdb-with-sql-plus.htm#ADMIN-GUID-D336011C-83B2-4101-B5AB-C4D890147BF4

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

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;

Tags: Oracle

Anju Garg

Written by Anju Garg

Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc.

She is a regular speaker at Sangam and OTNYathra. She writes articles about Oracle and is one of the reviewers of the following book published by Pearson Oracle Problem-Solving and Troubleshooting Handbook

She is certified for :

  • Oracle 9i Database Administration OCP
  • Oracle 11g Database Administration OCP
  • Oracle 11g Performance Tuning OCE
  • Oracle 11g R2 RAC OCE
  • Oracle 11g SQL Tuning OCE
  • Oracle 12c Database Administration OCP
  • Oracle Real Application Clusters 12c Certified Implementation Specialist

She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/