{"id":5436,"date":"2021-06-15T08:06:49","date_gmt":"2021-06-15T08:06:49","guid":{"rendered":"https:\/\/swiv.com.br\/querying-data-owned-by-common-users-in-multiple-pdbs\/"},"modified":"2026-05-27T20:02:31","modified_gmt":"2026-05-27T19:02:31","slug":"querying-data-owned-by-common-users-in-multiple-pdbs","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/06\/15\/querying-data-owned-by-common-users-in-multiple-pdbs\/","title":{"rendered":"Querying data owned by common users in multiple PDBs"},"content":{"rendered":"\n<p>Na arquitetura Multitenant, temos op\u00e7\u00e3o de realizar consulta a partir de um common user que consiga acessar dados de m\u00faltiplos PDBs, desde que as condi\u00e7\u00f5es abaixo sejam atendidas:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Al\u00e9m da exist\u00eancia da tabela\/view nos PDBs, ela deve existir tamb\u00e9m no CDB$ROOT (nem que seja apenas a sua estrutura DDL);<\/li><li>As tabelas\/views devem ter como owner um common user, e que a consulta seja a partir do mesmo;<\/li><li>A defini\u00e7\u00e3o dos objetos deve ser a mesma entre os containers, exceto pelo fato de podermos ter colunas a mais em um PDB comparado com o outro.<\/li><\/ul>\n\n\n\n<p>Validando a exist\u00eancia de um common user em meu laborat\u00f3rio:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@quiasma ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Tue Jun 15 04:33:13 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL&gt; SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME      OPEN_MODE            CDB\n--------- -------------------- ---\nASWAN     READ WRITE           YES\n \nSQL&gt; SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE &#039;%C##%&#039;;\n \nUSERNAME\n--------------------------------------------------------------------------------\nC##LUXOR\n<\/pre><\/div>\n\n\n<p>Validando a exist\u00eancia de 2 PDBs para testes (HIPOFISE1 e HIPOFISE2):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; show pdbs;\n \n    CON_ID CON_NAME                       OPEN MODE  RESTRICTED\n---------- ------------------------------ ---------- ----------\n         2 PDB$SEED                       READ ONLY  NO\n         3 HIPOFISE2                      READ WRITE NO\n         4 HIPOFISE1                      READ WRITE NO\n<\/pre><\/div>\n\n\n<p>Validando a exist\u00eancia da tablespace USERS no CDB$ROOT e demais PDBs:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT NAME FROM V$TABLESPACE WHERE NAME=&#039;USERS&#039; AND CON_ID in (1,3,4);\n \nNAME\n------------------------------\nUSERS\nUSERS\nUSERS\n<\/pre><\/div>\n\n\n<p>No CDB$ROOT, concedendo alguns privil\u00e9gio ao common user C##LUXOR, para todos os containers:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; GRANT CREATE SESSION TO C##LUXOR CONTAINER=ALL;\n \nGrant succeeded.\n \nSQL&gt; GRANT CREATE TABLE TO C##LUXOR CONTAINER=ALL;\n \nGrant succeeded.\n \nSQL&gt; GRANT ALTER SESSION TO C##LUXOR CONTAINER=ALL;\n \nGrant succeeded.\n \nSQL&gt; GRANT SET CONTAINER TO C##LUXOR CONTAINER=ALL;\n \nGrant succeeded.\n \nSQL&gt; GRANT UNLIMITED TABLESPACE TO C##LUXOR CONTAINER=ALL;\n \nGrant succeeded.\n<\/pre><\/div>\n\n\n<p>Logando no CDB$ROOT com o nosso common user, para criar uma tabela de teste (sem nenhum registro):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; conn C##LUXOR\/oracle\nConnected.\nSQL&gt; SHO USER;\nUSER is &quot;C##LUXOR&quot;\nSQL&gt; CREATE TABLE LOGIN_AUDIT ( RECORD_ID NUMBER, DB VARCHAR2(5), USER_ID VARCHAR2(5), LOGIN_TIME DATE, LOGOFF_TIME DATE, CONSTRAINT LOGIN_AUDIT_PK PRIMARY KEY(RECORD_ID) ) TABLESPACE USERS;\n \nTable created.\n<\/pre><\/div>\n\n\n<p>Alterando o container para o PDB HIPOFISE1, criando a tabela e populando-a com alguns registros:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL&gt; CREATE TABLE LOGIN_AUDIT ( RECORD_ID NUMBER, DB VARCHAR2(5), USER_ID VARCHAR2(5), LOGIN_TIME DATE, LOGOFF_TIME DATE, CONSTRAINT LOGIN_AUDIT_PK PRIMARY KEY(RECORD_ID) ) TABLESPACE USERS;\n \nTable created.\n \nSQL&gt; INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (11,&#039;PDB1&#039;,&#039;USER1&#039;, SYSDATE-1.5, SYSDATE-1.4);\n \n1 row created.\n \nSQL&gt; INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (21,&#039;PDB1&#039;,&#039;USER2&#039;, SYSDATE-1.6, SYSDATE-1.5);\n \n1 row created.\n \nSQL&gt; INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (31,&#039;PDB1&#039;,&#039;USER3&#039;, SYSDATE-1.7, SYSDATE-1.5);\n \n1 row created.\n \nSQL&gt; COMMIT;\n \nCommit complete.\n<\/pre><\/div>\n\n\n<p>Realizando mesmo processo no PDB HIPOFISE2:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER SESSION SET CONTAINER=HIPOFISE2;\n \nSession altered.\n \nSQL&gt; CREATE TABLE LOGIN_AUDIT ( RECORD_ID NUMBER, DB VARCHAR2(5), USER_ID VARCHAR2(5), LOGIN_TIME DATE, LOGOFF_TIME DATE, CONSTRAINT LOGIN_AUDIT_PK PRIMARY KEY(RECORD_ID) ) TABLESPACE USERS;\n \nTable created.\n \nSQL&gt; INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (12,&#039;PDB2&#039;,&#039;USER4&#039;, SYSDATE-2.5, SYSDATE-2.4);\n \n1 row created.\n \nSQL&gt; INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (22,&#039;PDB2&#039;,&#039;USER5&#039;, SYSDATE-2.6, SYSDATE-2.5);\n \n1 row created.\n \nSQL&gt; INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (32,&#039;PDB2&#039;,&#039;USER4&#039;, SYSDATE-2.7, SYSDATE-2.5);\n \n1 row created.\n \nSQL&gt; COMMIT;\n \nCommit complete.\n<\/pre><\/div>\n\n\n<p>Agora podemos simular as consultas em todos os PDBs. Utilizando a cl\u00e1usula &#8220;CONTAINER&#8221; no select, conforme exemplo abaixo, conseguimos acessar os dados da tabela de todos os PDBs em um \u00fanico comando (logicamente logados com o common user, no CDB$ROOT):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; conn C##LUXOR\/oracle\nConnected.\nSQL&gt; col con$name format a10\nSQL&gt; ALTER SESSION SET NLS_DATE_FORMAT=&#039;DD-MM-RR HH24:MI&#039;;\n \nSession altered.\n \nSQL&gt; SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT);\n \n RECORD_ID DB    USER_ LOGIN_TIME     LOGOFF_TIME        CON_ID CON$NAME\n---------- ----- ----- -------------- -------------- ---------- ----------\n        12 PDB2  USER4 12-06-21 16:47 12-06-21 19:11          3 HIPOFISE2\n        22 PDB2  USER5 12-06-21 14:23 12-06-21 16:47          3 HIPOFISE2\n        32 PDB2  USER4 12-06-21 11:59 12-06-21 16:47          3 HIPOFISE2\n        11 PDB1  USER1 13-06-21 16:46 13-06-21 19:10          4 HIPOFISE1\n        21 PDB1  USER2 13-06-21 14:22 13-06-21 16:46          4 HIPOFISE1\n        31 PDB1  USER3 13-06-21 11:58 13-06-21 16:46          4 HIPOFISE1\n \n6 rows selected.\n<\/pre><\/div>\n\n\n<p>O exemplo abaixo demonstra como podemos realizar a consulta dos PDBs filtrando o seu nome:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON$NAME IN (&#039;HIPOFISE1&#039;, &#039;HIPOFISE2&#039;);\n \n RECORD_ID DB    USER_ LOGIN_TIME     LOGOFF_TIME        CON_ID CON$NAME\n---------- ----- ----- -------------- -------------- ---------- ----------\n        12 PDB2  USER4 12-06-21 16:47 12-06-21 19:11          3 HIPOFISE2\n        22 PDB2  USER5 12-06-21 14:23 12-06-21 16:47          3 HIPOFISE2\n        32 PDB2  USER4 12-06-21 11:59 12-06-21 16:47          3 HIPOFISE2\n        11 PDB1  USER1 13-06-21 16:46 13-06-21 19:10          4 HIPOFISE1\n        21 PDB1  USER2 13-06-21 14:22 13-06-21 16:46          4 HIPOFISE1\n        31 PDB1  USER3 13-06-21 11:58 13-06-21 16:46          4 HIPOFISE1\n \n6 rows selected.\n \nSQL&gt; SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON$NAME IN (&#039;HIPOFISE1&#039;);\n \n RECORD_ID DB    USER_ LOGIN_TIME     LOGOFF_TIME        CON_ID CON$NAME\n---------- ----- ----- -------------- -------------- ---------- ----------\n        11 PDB1  USER1 13-06-21 16:46 13-06-21 19:10          4 HIPOFISE1\n        21 PDB1  USER2 13-06-21 14:22 13-06-21 16:46          4 HIPOFISE1\n        31 PDB1  USER3 13-06-21 11:58 13-06-21 16:46          4 HIPOFISE1\n<\/pre><\/div>\n\n\n<p>Por \u00faltimo, conseguimos realizar o mesmo processo citado acima, mas usando o filtro de CON_ID:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON_ID IN (3, 4);\n \n RECORD_ID DB    USER_ LOGIN_TIME     LOGOFF_TIME        CON_ID CON$NAME\n---------- ----- ----- -------------- -------------- ---------- ----------\n        12 PDB2  USER4 12-06-21 16:47 12-06-21 19:11          3 HIPOFISE2\n        22 PDB2  USER5 12-06-21 14:23 12-06-21 16:47          3 HIPOFISE2\n        32 PDB2  USER4 12-06-21 11:59 12-06-21 16:47          3 HIPOFISE2\n        11 PDB1  USER1 13-06-21 16:46 13-06-21 19:10          4 HIPOFISE1\n        21 PDB1  USER2 13-06-21 14:22 13-06-21 16:46          4 HIPOFISE1\n        31 PDB1  USER3 13-06-21 11:58 13-06-21 16:46          4 HIPOFISE1\n \n6 rows selected.\n \nSQL&gt; SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON_ID IN (3);\n \n RECORD_ID DB    USER_ LOGIN_TIME     LOGOFF_TIME        CON_ID CON$NAME\n---------- ----- ----- -------------- -------------- ---------- ----------\n        12 PDB2  USER4 12-06-21 16:47 12-06-21 19:11          3 HIPOFISE2\n        22 PDB2  USER5 12-06-21 14:23 12-06-21 16:47          3 HIPOFISE2\n        32 PDB2  USER4 12-06-21 11:59 12-06-21 16:47          3 HIPOFISE2\n<\/pre><\/div>\n\n\n<p>Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Na arquitetura Multitenant, temos op\u00e7\u00e3o de realizar consulta a partir de um common user que consiga acessar dados de m\u00faltiplos PDBs, desde que as condi\u00e7\u00f5es abaixo sejam atendidas: Al\u00e9m da exist\u00eancia da tabela\/view nos PDBs, ela deve existir tamb\u00e9m no CDB$ROOT (nem que seja apenas a sua estrutura DDL); As tabelas\/views devem ter como owner [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-5436","post","type-post","status-publish","format-standard","hentry","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5436","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=5436"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5436\/revisions"}],"predecessor-version":[{"id":9103,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5436\/revisions\/9103"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5436"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5436"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5436"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}