Na arquitetura Multitenant, temos opção de realizar consulta a partir de um common user que consiga acessar dados de múltiplos PDBs, desde que as condições abaixo sejam atendidas:
- Além da existência da tabela/view nos PDBs, ela deve existir também no CDB$ROOT (nem que seja apenas a sua estrutura DDL);
- As tabelas/views devem ter como owner um common user, e que a consulta seja a partir do mesmo;
- A definição 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.
Validando a existência de um common user em meu laboratório:
[oracle@quiasma ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jun 15 04:33:13 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE CDB
--------- -------------------- ---
ASWAN READ WRITE YES
SQL> SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE '%C##%';
USERNAME
--------------------------------------------------------------------------------
C##LUXOR
Validando a existência de 2 PDBs para testes (HIPOFISE1 e HIPOFISE2):
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HIPOFISE2 READ WRITE NO
4 HIPOFISE1 READ WRITE NO
Validando a existência da tablespace USERS no CDB$ROOT e demais PDBs:
SQL> SELECT NAME FROM V$TABLESPACE WHERE NAME='USERS' AND CON_ID in (1,3,4);
NAME
------------------------------
USERS
USERS
USERS
No CDB$ROOT, concedendo alguns privilégio ao common user C##LUXOR, para todos os containers:
SQL> GRANT CREATE SESSION TO C##LUXOR CONTAINER=ALL;
Grant succeeded.
SQL> GRANT CREATE TABLE TO C##LUXOR CONTAINER=ALL;
Grant succeeded.
SQL> GRANT ALTER SESSION TO C##LUXOR CONTAINER=ALL;
Grant succeeded.
SQL> GRANT SET CONTAINER TO C##LUXOR CONTAINER=ALL;
Grant succeeded.
SQL> GRANT UNLIMITED TABLESPACE TO C##LUXOR CONTAINER=ALL;
Grant succeeded.
Logando no CDB$ROOT com o nosso common user, para criar uma tabela de teste (sem nenhum registro):
SQL> conn C##LUXOR/oracle
Connected.
SQL> SHO USER;
USER is "C##LUXOR"
SQL> 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;
Table created.
Alterando o container para o PDB HIPOFISE1, criando a tabela e populando-a com alguns registros:
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> 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;
Table created.
SQL> INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (11,'PDB1','USER1', SYSDATE-1.5, SYSDATE-1.4);
1 row created.
SQL> INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (21,'PDB1','USER2', SYSDATE-1.6, SYSDATE-1.5);
1 row created.
SQL> INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (31,'PDB1','USER3', SYSDATE-1.7, SYSDATE-1.5);
1 row created.
SQL> COMMIT;
Commit complete.
Realizando mesmo processo no PDB HIPOFISE2:
SQL> ALTER SESSION SET CONTAINER=HIPOFISE2;
Session altered.
SQL> 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;
Table created.
SQL> INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (12,'PDB2','USER4', SYSDATE-2.5, SYSDATE-2.4);
1 row created.
SQL> INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (22,'PDB2','USER5', SYSDATE-2.6, SYSDATE-2.5);
1 row created.
SQL> INSERT INTO LOGIN_AUDIT (RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME) VALUES (32,'PDB2','USER4', SYSDATE-2.7, SYSDATE-2.5);
1 row created.
SQL> COMMIT;
Commit complete.
Agora podemos simular as consultas em todos os PDBs. Utilizando a cláusula “CONTAINER” no select, conforme exemplo abaixo, conseguimos acessar os dados da tabela de todos os PDBs em um único comando (logicamente logados com o common user, no CDB$ROOT):
SQL> conn C##LUXOR/oracle
Connected.
SQL> col con$name format a10
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-RR HH24:MI';
Session altered.
SQL> SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT);
RECORD_ID DB USER_ LOGIN_TIME LOGOFF_TIME CON_ID CON$NAME
---------- ----- ----- -------------- -------------- ---------- ----------
12 PDB2 USER4 12-06-21 16:47 12-06-21 19:11 3 HIPOFISE2
22 PDB2 USER5 12-06-21 14:23 12-06-21 16:47 3 HIPOFISE2
32 PDB2 USER4 12-06-21 11:59 12-06-21 16:47 3 HIPOFISE2
11 PDB1 USER1 13-06-21 16:46 13-06-21 19:10 4 HIPOFISE1
21 PDB1 USER2 13-06-21 14:22 13-06-21 16:46 4 HIPOFISE1
31 PDB1 USER3 13-06-21 11:58 13-06-21 16:46 4 HIPOFISE1
6 rows selected.
O exemplo abaixo demonstra como podemos realizar a consulta dos PDBs filtrando o seu nome:
SQL> SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON$NAME IN ('HIPOFISE1', 'HIPOFISE2');
RECORD_ID DB USER_ LOGIN_TIME LOGOFF_TIME CON_ID CON$NAME
---------- ----- ----- -------------- -------------- ---------- ----------
12 PDB2 USER4 12-06-21 16:47 12-06-21 19:11 3 HIPOFISE2
22 PDB2 USER5 12-06-21 14:23 12-06-21 16:47 3 HIPOFISE2
32 PDB2 USER4 12-06-21 11:59 12-06-21 16:47 3 HIPOFISE2
11 PDB1 USER1 13-06-21 16:46 13-06-21 19:10 4 HIPOFISE1
21 PDB1 USER2 13-06-21 14:22 13-06-21 16:46 4 HIPOFISE1
31 PDB1 USER3 13-06-21 11:58 13-06-21 16:46 4 HIPOFISE1
6 rows selected.
SQL> SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON$NAME IN ('HIPOFISE1');
RECORD_ID DB USER_ LOGIN_TIME LOGOFF_TIME CON_ID CON$NAME
---------- ----- ----- -------------- -------------- ---------- ----------
11 PDB1 USER1 13-06-21 16:46 13-06-21 19:10 4 HIPOFISE1
21 PDB1 USER2 13-06-21 14:22 13-06-21 16:46 4 HIPOFISE1
31 PDB1 USER3 13-06-21 11:58 13-06-21 16:46 4 HIPOFISE1
Por último, conseguimos realizar o mesmo processo citado acima, mas usando o filtro de CON_ID:
SQL> SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON_ID IN (3, 4);
RECORD_ID DB USER_ LOGIN_TIME LOGOFF_TIME CON_ID CON$NAME
---------- ----- ----- -------------- -------------- ---------- ----------
12 PDB2 USER4 12-06-21 16:47 12-06-21 19:11 3 HIPOFISE2
22 PDB2 USER5 12-06-21 14:23 12-06-21 16:47 3 HIPOFISE2
32 PDB2 USER4 12-06-21 11:59 12-06-21 16:47 3 HIPOFISE2
11 PDB1 USER1 13-06-21 16:46 13-06-21 19:10 4 HIPOFISE1
21 PDB1 USER2 13-06-21 14:22 13-06-21 16:46 4 HIPOFISE1
31 PDB1 USER3 13-06-21 11:58 13-06-21 16:46 4 HIPOFISE1
6 rows selected.
SQL> SELECT RECORD_ID, DB, USER_ID, LOGIN_TIME, LOGOFF_TIME, CON_ID, CON$NAME FROM CONTAINERS(LOGIN_AUDIT) WHERE CON_ID IN (3);
RECORD_ID DB USER_ LOGIN_TIME LOGOFF_TIME CON_ID CON$NAME
---------- ----- ----- -------------- -------------- ---------- ----------
12 PDB2 USER4 12-06-21 16:47 12-06-21 19:11 3 HIPOFISE2
22 PDB2 USER5 12-06-21 14:23 12-06-21 16:47 3 HIPOFISE2
32 PDB2 USER4 12-06-21 11:59 12-06-21 16:47 3 HIPOFISE2
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish