.
\nMultitenant 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.
\n<\/p>\n
Here is my CDB with two PDBs<\/p>\n
\n22:48:13 SQL> connect sys\/oracle@\/\/localhost\/CDB1A as sysdba\nConnected.\n \n22:48:13 SQL> show pdbs\n \nCON_ID CON_NAME OPEN MODE RESTRICTED\n------ -------- ---- ---- ----------\n2 PDB$SEED READ ONLY NO\n3 PDB1 READ WRITE NO\n4 PDB2 READ WRITE NO\n \n<\/code><\/pre>\nI create a DEMO table in CDB$ROOT and do the same in PDB1 and PDB2<\/p>\n
\n22:48:13 SQL> create table DEMO (n number primary key, text varchar2(90) );\nTable DEMO created.\n \n22:48:14 SQL> connect sys\/oracle@\/\/localhost\/PDB1 as sysdba\nConnected.\n \n22:48:14 SQL> create table DEMO (n number primary key, text varchar2(90) );\nTable DEMO created.\n \n22:48:14 SQL> connect sys\/oracle@\/\/localhost\/PDB2 as sysdba\nConnected.\n \n22:48:14 SQL> create table DEMO (n number primary key, text varchar2(90) );\nTable DEMO created.\n<\/code><\/pre>\nI connect to CDB$ROOT and set a transaction name, then check all transactions<\/p>\n
\n22:48:14 SQL> connect sys\/oracle@\/\/localhost\/CDB1A as sysdba\nConnected.\n \n22:48:14 SQL> set transaction name 'Franck';\nTransaction NAME succeeded.\n \n22:48:14 SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);\nno rows selected\n \n<\/code><\/pre>\nI’m alone here with no transactions.<\/p>\n
CONTAINERS()<\/h3>\n
Here is the cross-container syntax: using the CONTAINERS() and specifying the CON_ID column and value (CON_ID=3 for PDB1)<\/p>\n
\n22:48:14 SQL> insert into containers(DEMO) (con_id,n,text) values (3,1,'Cross-container insert');\n1 row inserted.\n \n22:48:14 SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);\n \nCON_ID ADDR XIDUSN UBAFIL SES_ADDR PTX_XID NAME USED_UREC\n------ ---- ------ ------ -------- ------- ---- ---------\n1 0000000067BB19E8 7 0 000000006ADD2EA8 0000000000000000 Franck 1\n3 000000006642AEB8 1 62 000000006AC99610 0000000000000000 2\n<\/code><\/pre>\nThe 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.<\/p>\n
I’m doing the same for PDB2 which is CON_ID=4<\/p>\n
\n22:48:14 SQL> insert into containers(DEMO) (con_id,n,text) values (4,1,'Cross-container insert');\n1 row inserted.\n \n22:48:15 SQL> select addr,con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);\n \nADDR CON_ID ADDR XIDUSN UBAFIL SES_ADDR PTX_XID NAME USED_UREC\n---- ------ ---- ------ ------ -------- ------- ---- ---------\n0000000067BB19E8 1 0000000067BB19E8 7 0 000000006ADD2EA8 0000000000000000 Franck 1\n000000006642AEB8 3 000000006642AEB8 1 62 000000006AC99610 0000000000000000 2\n000000006644EA90 4 000000006644EA90 6 66 000000006B20F828 0000000000000000 2\n<\/code><\/pre>\nlooking at the transactions sessions, the ones on the PDBs looks like a database link connection:<\/p>\n
\n22:48:15 SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);\n \nTADDR CON_ID PROGRAM ACTION MODULE\n----- ------ ------- ------ ------\n000000006644EA90 4 oracle@VM104 (TNS V1-V3) oracle@VM104 (TNS V1-V3)\n000000006642AEB8 3 oracle@VM104 (TNS V1-V3) oracle@VM104 (TNS V1-V3)\n0000000067BB19E8 1 java@VM104 (TNS V1-V3) java@VM104 (TNS V1-V3)\n<\/code><\/pre>\nIt looks as database links, and we can actually see those open links in V$DBLINKS:<\/p>\n
\n23:06:53 SQL> select * from v$dblink;\n \nDB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH CON_ID\n------- -------- --------- ------------- -------- ------------ -------------- ----------- --------------------- ------\nPDB1 0 YES YES UNKN 0 YES YES 1 1\nPDB2 0 YES YES UNKN 0 YES YES 1 1\n<\/code><\/pre>\nCommit<\/h3>\n
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:<\/p>\n
\n22:48:15 SQL> select * from containers(DEMO);\nno rows selected\n<\/code><\/pre>\nNow, I commit:<\/p>\n
\n22:48:15 SQL> commit;\nCommit complete.\n<\/code><\/pre>\nand all transactions are ended:<\/p>\n
\n22:48:15 SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);\nno rows selected\n<\/code><\/pre>\nthe links are still opened but not in a transaction anymore:<\/p>\n
\n23:10:21 SQL> select * from v$dblink;\n \nDB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH CON_ID\n------- -------- --------- ------------- -------- ------------ -------------- ----------- --------------------- ------\nPDB1 0 YES YES UNKN 0 NO NO 1 1\nPDB2 0 YES YES UNKN 0 NO NO 1 1\n<\/code><\/pre>\nMy inserts are now visible, either from the root with CONTAINER()<\/p>\n
\n22:48:15 SQL> select * from containers(DEMO);\n \nN TEXT CON_ID\n- ---- ------\n1 Cross-container insert 4\n1 Cross-container insert 3\n<\/code><\/pre>\nor from each PDB:<\/p>\n
\n22:48:15 SQL> alter session set container=PDB1;\nSession altered.\n \n22:48:15 SQL> select * from DEMO;\n \nN TEXT\n- ----\n1 Cross-container insert\n<\/code><\/pre>\nSo what?<\/h3>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[656,220,64,96,209,66,223],"type_dbi":[],"class_list":["post-10051","post","type-post","status-publish","format-standard","hentry","category-database-management","tag-12-2","tag-cdb","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"acf":[],"yoast_head":"\n
12cR2 Cross-container DML - insert into container() - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12cR2 Cross-container DML - insert into container()\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-05-13T13:07:53+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12cR2 Cross-container DML – insert into container()\",\"datePublished\":\"2017-05-13T13:07:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/\"},\"wordCount\":404,\"commentCount\":0,\"keywords\":[\"12.2\",\"CDB\",\"multitenant\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/\",\"name\":\"12cR2 Cross-container DML - insert into container() - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-05-13T13:07:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12cR2 Cross-container DML – insert into container()\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"12cR2 Cross-container DML - insert into container() - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/","og_locale":"en_US","og_type":"article","og_title":"12cR2 Cross-container DML - insert into container()","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/","og_site_name":"dbi Blog","article_published_time":"2017-05-13T13:07:53+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12cR2 Cross-container DML – insert into container()","datePublished":"2017-05-13T13:07:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/"},"wordCount":404,"commentCount":0,"keywords":["12.2","CDB","multitenant","Oracle","Oracle 12c","PDB","Pluggable Databases"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/","url":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/","name":"12cR2 Cross-container DML - insert into container() - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-05-13T13:07:53+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-cross-container-dml-insert-into-container\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12cR2 Cross-container DML – insert into container()"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10051","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10051"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10051\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10051"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}