Can multiple application PDBs be updated in a single DML?

    Aug 22, 2017 1:54:36 PM by Anju Garg

    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.

     

    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:

    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/