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.
- For
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.customersis 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.customersis 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.customersin 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:
Start the discussion at forums.toadworld.com