Infrastructure at your Service

Franck Pachot

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

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 oracle@VM104 (TNS V1-V3) oracle@VM104 (TNS V1-V3)
000000006642AEB8 3 oracle@VM104 (TNS V1-V3) oracle@VM104 (TNS V1-V3)
0000000067BB19E8 1 java@VM104 (TNS V1-V3) java@VM104 (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.

 

Leave a Reply


× 3 = eighteen

Franck Pachot
Franck Pachot

Technology Leader