Complete separation of pluggable databases is naturally a good thing when it comes to protecting the rights and data of the stakeholders of those databases. For example, on a shared infrastructure system, you really want that isolation from your unknown neighbours sharing the hardware.
But for many customers, in particular, those running on-premises, whilst the separation of pluggables is still important, there are also benefits to be gained from being able to view those databases as a single entity. The multi-tenant option provides this already for things like backups and background processes etc, but it is also possible access data from multiple pluggables with a single SQL using the CONTAINERS clause.
Here’s a quick example of that where the “DB19_CDB” connection equates to my root container, and “DB19_PDB1” equates to a pluggable database sitting within it.
SQL> conn system/admin@db19_cdb -- root container (CON=1) Connected. SQL> create table ttt ( x int ); Table created. SQL> insert into ttt values (1); 1 row created. SQL> commit; Commit complete. SQL> select * from containers(ttt); X CON_ID ---------- ---------- 1 1 SQL> insert into containers(ttt) (con_id,x) values (1,100); 1 row created. SQL> commit; Commit complete. SQL> conn system/admin@db19_pdb1 -- pluggable (CON=3) Connected. SQL> create table ttt ( x int ); Table created. SQL> insert into ttt values (2); 1 row created. SQL> commit; Commit complete. SQL> insert into containers(ttt) (con_id,x) values (3,200); 1 row created. SQL> commit; Commit complete. SQL> conn system/admin@db19_cdb Connected. SQL> select * from containers(ttt); X CON_ID ---------- ---------- 1 1 100 1 2 3 200 3 4 rows selected.
I’ve created the table TTT in each container, and yes before you take me to task in the comments, I know I shouldn’t be using SYSTEM but I wanted to focus here on the CONTAINERS clause, not get distracted by a local versus common user discussion.
Notice some of the cool things about this facility
- I automatically get a CON_ID column to tell me which container the row belongs to
- When inserting a row, I can also reference the CON_ID column even those the table does not physically have that column defined.
- We still protect the security of the data, namely, a pluggable database only sees its own data, whilst the root can span all of its child pluggables. (You’ll see similar behaviour if you delve into the realm of application containers)
The fact that I can nominate the container ID when using an INSERT statement suggests the possibility of being able to insert data from the root into a pluggable database’s copy of the table. If you’re using a database configured with all of the defaults, then this will probably work straight out of the box. However, you might also get this
SQL> conn system/admin@db19_cdb Connected. SQL> insert into containers(ttt) (con_id,x) values (3,300); insert into containers(ttt) (con_id,x) values (3,300) * ERROR at line 1: ORA-12541: TNS:no listener
It seems very odd that I would encounter any kind of SQL*Net error given that this is all occurring within the same database on the same node. This is the first hint as to what the CONTAINERS clause might be doing “under the covers” to implement the cross-pluggable functionality. If you take a look in the alert.log after you get this error, you’ll see messages similar to the following.
Fatal NI connect error 12541, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gtx)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=0cecdca0ba694c2ba0c9a4f691e29489) (CID=(PROGRAM=c:\oracle\product\19\bin\ORACLE.EXE)(HOST=GTX)(USER=hamcdc))))
Notice that there was a connection attempt to the host “gtx” (the laptop I’m running this demo on) through port 1521. That struck as very odd because the database and listener on this box are all configured to use a port of 1519! (I do this to allow me to run various versions of listeners and databases on the same machine – 1518 for 18c, 1519 for 19c and so forth). You can see from the database parameters and LSNRCTL output, the 1519 is the port I’ve configured.
SQL> show parameter local_liste NAME TYPE VALUE ------------------------------------ ----------- -------------------- local_listener string LISTENER_DB19 ## from tnsnames.ora LISTENER_DB19 = (ADDRESS = (PROTOCOL = TCP)(HOST = gtx)(PORT = 1519)) C:\>lsnrctl status listener19 LSNRCTL for 64-bit Windows: Version 18.104.22.168.0 - Production on 29-APR-2020 11:55:21 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gtx)(PORT=1519))) STATUS of the LISTENER ------------------------ Alias LISTENER19 Version TNSLSNR for 64-bit Windows: Version 22.214.171.124.0 - Production Start Date 20-APR-2020 10:07:08 Uptime 9 days 1 hr. 48 min. 13 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\oracle\product\19\network\admin\listener.ora Listener Log File C:\oracle\diag\tnslsnr\gtx\listener19\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gtx)(PORT=1519))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1519ipc))) Services Summary... ... ...
Thus the big question is – where does the usage of 1521 come from in the CONTAINERS query clause? After much digging around, I discovered that these are properties that can you see for the pluggable databases themselves:
SQL> ALTER PLUGGABLE DATABASE CONTAINERS PORT=1519; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE CONTAINERS HOST='gtx'; Pluggable database altered.
Once this was done, I could now run DML from the root into my pluggable database without getting the error.
SQL> insert into containers(ttt) (con_id,x) values (3,300); 1 row created.
The fact that this operation is being done over SQL*Net reveals another interesting discovery. It would appear that queries are being done via separate database connections, because whilst I have just inserted the value “300” in the INSERT command above, it does not appear in the subsequent query
SQL> select * from containers(ttt); X CON_ID ---------- ---------- 1 1 100 1 2 3 200 3 4 rows selected.
Only once I commit the transaction does the row appear in the CONTAINERS clause query.
SQL> commit; Commit complete. SQL> select * from containers(ttt); X CON_ID ---------- ---------- 1 1 100 1 2 3 200 3 300 3 5 rows selected.
So keep these things in mind if you’re building application infrastructure around the CONTAINERS clause. It is not “just another SQL query”
For a longer form treatment of the CONTAINERS clause, check out the video below.