Default target container for DML operations

    Aug 22, 2017 3:20:35 PM by Anju Garg

    Introduction

    For an application container, when a DML statement is issued in the application root without specifying containers in the WHERE clause, the DML statement affects the default container for that application container.

    As for the default value of default target containers for DML operations, Oracle documentation is misleading and self-contradictory. . Here are some excerpts with relevant parts highlighted:

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

    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;

    By default, the default target containers in an application container include all of its application PDBs but not its application root or application seed.

    Example 44-13 Specifying the Default Container for DML Statements in an Application Container

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

    ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = (APDB1);

    Example 44-14 Clearing the Default Container

    This example clears the default container setting. When it is not set, the default container is the application root.

    ALTER PLUGGABLE DATABASE CONTAINERS DEFAULT TARGET = NONE;

    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.
      • If you specify NONE, then the default container is the CDB root. This is the default.

    When a DML statement is issued in the application root without specifying containers in the WHERE clause, the DML statement affects the default container for the application container.

    As can be seen, Oracle documentation mentions the following three different default values for the default target container for DML operations :

    • All the application PDBs in an application container but not its application root or application seed
    • Application root
    • CDB root

    Obviously, not all three can be true at the same time.

     

    Finding the actual default value of the default target container for DML operations

    In order to find the actual default value of the default target container for DML operations, 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.

    Let us verify that table sales_app_user.customers is not enabled to collaborate with the container map.

     SALES_APP_ROOT>SELECT container_map, table_name
                         FROM dba_tables
                         WHERE owner='SALES_APP_USER'
                         and table_name = 'CUSTOMERS';
      
    CONTAINER_MAP    TABLE_NAME
    --------------- ----------
    NO               CUSTOMERS

    Now I will insert a record in the table sales_app_user.customers in the application root container sales_app_root (con_id = 3).

    SALES_APP_ROOT>insert into containers(sales_app_user.customers)
               (con_id,cust_id,cust_name,cust_add, cust_zone) values
               (3,      'R1', 'Cust1(root)','USA(root) address', 'R');
     
               commit; 
     
    SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
                          cust.CUST_Zone, con.name con_name
                   from   containers(sales_app_user.customers) cust,
                       v$containers con
                   where cust.con_id = 3 and cust.con_id = con.con_id;
     
    CUST_ID CUST_NAME     CUST_ADD                 CUST_ZONE CON_NAME
    ------- ------------- ----------------------- --------- ------------
    R1      Cust1(root)    USA(root) address       R          SALES_APP_ROOT

    Let us clear the setting, (if any) of the default container for DML operations for our application container so that it is at its default value.

    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
     
    Now that default container is not set, I will issue an update statement without a WHERE clause
    SALES_APP_ROOT>UPDATE CONTAINERS(sales_app_user.customers) ctab
                    SET ctab.cust_add ='New ' || cust_add;
     
    1 row updated.
     
    SALES_APP_ROOT>commit;  
    Let us check the record that has been updated
    SALES_APP_ROOT>select cust.CUST_ID, cust.CUST_NAME, cust.CUST_ADD,
                          cust.CUST_Zone, 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_NAME
    ------- ------------ ----------------------- ---------- -------------
    R1       Cust1(root)   New USA(root) address   R          SALES_APP_ROOT
    N1       North_Cust_1  North_Cust_1_address    N           NORTH_APP_PDB
    N2       North_Cust_2  North_Cust_2_address    N           NORTH_APP_PDB
    N3       North_Cust_3  North_Cust_3_address    N            NORTH_APP_PDB
    N4       North_Cust_4  North_Cust_4_address    N           NORTH_APP_PDB
    S1       South_Cust_1  South_Cust_1_address    S             SOUTH_APP_PDB
    S2       South_Cust_2  South_Cust_2_address    S           SOUTH_APP_PDB
    S3       South_Cust_3  South_Cust_3_address    S           SOUTH_APP_PDB
    S4       South_Cust_4  South_Cust_4_address    S           SOUTH_APP_PDB
    W1       West_Cust_1   West_Cust_1_address     W             WEST_APP_PDB
    W2       West_Cust_2   West_Cust_2_address     W           WEST_APP_PDB
    W3       West_Cust_3   West_Cust_3_address     W           WEST_APP_PDB
    W4       West_Cust_4   West_Cust_4_address     W           WEST_APP_PDB
    E1       East_Cust_1   East_Cust_1_address     E            EAST_APP_PDB
    E2       East_Cust_2   East_Cust_2_address     E           EAST_APP_PDB
    E3       East_Cust_3   East_Cust_3_address     E           EAST_APP_PDB
    E4       East_Cust_4   East_Cust_4_address     E           EAST_APP_PDB
     
    17 rows selected.

     

    Conclusion

    It can be seen that the record created in the application root has been updated, which verifies that When it is not set, the default container for DML operations in an application container is the application root.

     

    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/