12cR2 Cross-container DML – insert into container()

By Franck Pachot

.
Multitenant has been introduced in 12.1.0.1 with the goal to share resources but isolate data. However, having all PDBs in the same root may be convenient to manipulate data in multiple PDBs. In the first patchset, 12.1.0.2, a way to query cross-container has been introduced for the CDB administrator to see data in other containers. In the second release, 12.2.0.1, this goes further with the introduction of Application Containers and cross-PDB DML. Currently, not all possibilities are documented and not all documented features are actually working. This will probably improve in next patchset. I’ll start here with something simple: insert from root into a table which is in a PDB.

Here is my CDB with two PDBs

22:48:13 SQL> connect sys/oracle@//localhost/CDB1A as sysdba
Connected.
 
22:48:13 SQL> show pdbs
 
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------  --------  ---- ----   ----------
2       PDB$SEED  READ ONLY   NO
3       PDB1      READ WRITE  NO
4       PDB2      READ WRITE  NO
 

I create a DEMO table in CDB$ROOT and do the same in PDB1 and PDB2

22:48:13 SQL> create table DEMO (n number primary key, text varchar2(90) );
Table DEMO created.
 
22:48:14 SQL> connect sys/oracle@//localhost/PDB1  as sysdba
Connected.
 
22:48:14 SQL> create table DEMO (n number primary key, text varchar2(90) );
Table DEMO created.
 
22:48:14 SQL> connect sys/oracle@//localhost/PDB2  as sysdba
Connected.
 
22:48:14 SQL> create table DEMO (n number primary key, text varchar2(90) );
Table DEMO created.

I connect to CDB$ROOT and set a transaction name, then check all transactions

22:48:14 SQL> connect sys/oracle@//localhost/CDB1A as sysdba
Connected.
 
22:48:14 SQL> set transaction name 'Franck';
Transaction NAME succeeded.
 
22:48:14 SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);
no rows selected
 

I’m alone here with no transactions.

CONTAINERS()

Here is the cross-container syntax: using the CONTAINERS() and specifying the CON_ID column and value (CON_ID=3 for PDB1)

22:48:14 SQL> insert into containers(DEMO) (con_id,n,text) values (3,1,'Cross-container insert');
1 row inserted.
 
22:48:14 SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);
 
CON_ID  ADDR              XIDUSN  UBAFIL  SES_ADDR          PTX_XID           NAME    USED_UREC
------  ----              ------  ------  --------          -------           ----    ---------
1       0000000067BB19E8  7       0       000000006ADD2EA8  0000000000000000  Franck  1
3       000000006642AEB8  1       62      000000006AC99610  0000000000000000          2

The interesting thing is that I have two transactions: one on my current container, and one on the container CON_ID=3 specified in my insert.

I’m doing the same for PDB2 which is CON_ID=4

22:48:14 SQL> insert into containers(DEMO) (con_id,n,text) values (4,1,'Cross-container insert');
1 row inserted.
 
22:48:15 SQL> select addr,con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);
 
ADDR              CON_ID  ADDR              XIDUSN  UBAFIL  SES_ADDR          PTX_XID           NAME    USED_UREC
----              ------  ----              ------  ------  --------          -------           ----    ---------
0000000067BB19E8  1       0000000067BB19E8  7       0       000000006ADD2EA8  0000000000000000  Franck  1
000000006642AEB8  3       000000006642AEB8  1       62      000000006AC99610  0000000000000000          2
000000006644EA90  4       000000006644EA90  6       66      000000006B20F828  0000000000000000          2

looking at the transactions sessions, the ones on the PDBs looks like a database link connection:

22:48:15 SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);
 
TADDR             CON_ID  PROGRAM                   ACTION  MODULE
-----             ------  -------                   ------  ------
000000006644EA90  4       [email protected] (TNS V1-V3)          [email protected] (TNS V1-V3)
000000006642AEB8  3       [email protected] (TNS V1-V3)          [email protected] (TNS V1-V3)
0000000067BB19E8  1       [email protected] (TNS V1-V3)            [email protected] (TNS V1-V3)

It looks as database links, and we can actually see those open links in V$DBLINKS:

23:06:53 SQL> select * from v$dblink;
 
DB_LINK  OWNER_ID  LOGGED_ON  HETEROGENEOUS  PROTOCOL  OPEN_CURSORS  IN_TRANSACTION  UPDATE_SENT  COMMIT_POINT_STRENGTH  CON_ID
-------  --------  ---------  -------------  --------  ------------  --------------  -----------  ---------------------  ------
PDB1     0         YES        YES            UNKN      0             YES             YES          1                      1
PDB2     0         YES        YES            UNKN      0             YES             YES          1                      1

Commit

However, when using CONTAINERS() the session is not using the database links but something like parallel query switching to the containers. This means that it is not the same transaction and we don’t see the modifications:

22:48:15 SQL> select * from containers(DEMO);
no rows selected

Now, I commit:

22:48:15 SQL> commit;
Commit complete.

and all transactions are ended:

22:48:15 SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);
no rows selected

the links are still opened but not in a transaction anymore:

23:10:21 SQL> select * from v$dblink;
 
DB_LINK  OWNER_ID  LOGGED_ON  HETEROGENEOUS  PROTOCOL  OPEN_CURSORS  IN_TRANSACTION  UPDATE_SENT  COMMIT_POINT_STRENGTH  CON_ID
-------  --------  ---------  -------------  --------  ------------  --------------  -----------  ---------------------  ------
PDB1     0         YES        YES            UNKN      0             NO              NO           1                      1
PDB2     0         YES        YES            UNKN      0             NO              NO           1                      1

My inserts are now visible, either from the root with CONTAINER()

22:48:15 SQL> select * from containers(DEMO);
 
N  TEXT                    CON_ID
-  ----                    ------
1  Cross-container insert  4
1  Cross-container insert  3

or from each PDB:

22:48:15 SQL> alter session set container=PDB1;
Session altered.
 
22:48:15 SQL> select * from DEMO;
 
N  TEXT
-  ----
1  Cross-container insert

So what?

This is a convenient way for the CDB administrator, or for the Application Root administrator, to do some DML on different containers, without having to create a database link. Of course, the common user can also switch to a PDB using the ‘alter session set container’ but this one does not allow to have a transaction that spans multiple containers. You can think of it as a shortcut to avoid creating database links from the root to its containers.