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:

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: