在Oracle Database 12.2多租户架构中,使用Containers关键字来完成跨pdb查询,该表在各个pdb中有相同表名,相同表结构
考虑到MSsqlServer有跨库查询的语法,(以sa登录,select* from database_name.user_name.tab_name),
那么,在12c多租户下怎么实现这种跨库的查询呢?
https://docs.oracle.com/database/122/ADMIN/viewing-information-about-cdbs-and-pdbs-with-sql-plus.htm#ADMIN14319
大体过程:
在pdb highgo3下建立hgu3.t1这个table. 在pdb highgo2下建立hgu3.t1这个table. 在root中建立common user--C##nasa,并赋予C##nasa对pdb highgo3下 hgu3.t1表的select权限 并赋予C##nasa对pdb highgo2下 hgu3.t1表的select权限 在pdb highgo3中建立c##nasa.v_t1这个视图. 在pdb highgo2中建立c##nasa.v_t1这个视图. 在root中建立c##nasa.v_t1这个表. 在root中执行查询语句:select * from containers(v_t1) where con_id =4;
======================================pdb highgo3开始======================================== sql> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 HIGHGO1 MOUNTED 4 HIGHGO2 READ WRITE NO 5 HIGHGO3 READ WRITE NO sql> alter session set container=highgo3; Session altered. sql> create user hgu3 identified by aaaaaa; User created. sql> grant dba to hgu3; Grant succeeded. sql> conn hgu3/aaaaaa@highgo3 Connected. sql> create table t1 (c1 int,c3 char(2)); -------->在pdb highgo3下建立hgu3.t1这个table. Table created. sql> insert into t1 values(1,'AA'); 1 row created. sql> commit; Commit complete. sql> select * from t1; C1 C3 ---------- -- 1 AA sql> ======================================pdb highgo3结束======================================== ======================================pdb highgo2开始======================================== sql> conn / as sysdba Connected. sql> alter session set container=highgo2; Session altered. sql> create user hgu3 identified by aaaaaa; User created. sql> grant dba to hgu3; Grant succeeded. sql> conn hgu3/aaaaaa@highgo2; Connected. sql> create table t1 (c1 int,c3 char(2)); -------->在pdb highgo2下建立hgu3.t1这个table. Table created. sql> insert into t1 values(2,'BB') 2 / 1 row created. sql> commit; Commit complete. sql> ======================================pdb highgo2结束======================================== sql> show con_name CON_NAME ------------------------------ CDB$ROOT sql> show user USER is "SYS" sql> create user c##NASA identified by aaaaaa; ---->>>在root中建立common user. User created. sql> grant dba to c##nasa container=all; Grant succeeded. sql> sql> sql> conn sys/aaaaaa@highgo3 as sysdba Connected. sql> show con_name CON_NAME ------------------------------ HIGHGO3 sql> show user USER is "SYS" sql> grant select on hgu3.t1 to c##nasa; ---->>>在pdb highgo3中授予common user c##nasa 对hgu3.t1表的select权限. Grant succeeded. sql> sql> conn sys/aaaaaa@highgo2 as sysdba ---->>>在pdb highgo2中授予common user c##nasa 对hgu3.t1表的select权限. Connected. sql> grant select on hgu3.t1 to c##nasa; Grant succeeded. sql> sql> conn c##nasa/aaaaaa@highgo3 Connected. sql> create view v_t1 as select * from hgu3.t1;---->>>在pdb highgo3中建立c##nasa.v_t1这个视图. View created. sql> conn c##nasa/aaaaaa@highgo2 Connected. sql> create view v_t1 as select * from hgu3.t1;---->>>在pdb highgo2中建立c##nasa.v_t1这个视图. View created. sql> sql> conn c##nasa/aaaaaa@cdb$root Connected. sql> show user USER is "C##NASA" sql> show con_name CON_NAME ------------------------------ CDB$ROOT sql> create table v_t1 (c1 int,c3 char(2));;---->>>在root中建立c##nasa.v_t1这个表. Table created. sql> sql> select * from containers(v_t1); ----->>>查询语法如下. C1 C3 CON_ID ---------- -- ---------- 1 AA 5 2 BB 4 sql> sql> select * from containers(v_t1) where con_id =4; ----->>>查询语法如下. C1 C3 CON_ID ---------- -- ---------- 2 BB 4 sql> sql> explain plan for select * from containers(v_t1) where con_id =4; Explained. sql> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1051007651 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 400 | 12000 | 1 (100)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 400 | 12000 | 1 (100)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION LIST SINGLE| | 400 | 12000 | 1 (100)| 00:00:01 | 4 | 4 | Q1,00 | PCWC | | | 4 | CONTAINERS FULL | V_T1 | 400 | 12000 | 1 (100)| 00:00:01 | | | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------ 11 rows selected. --------->>>上面还用了并行. sql> SET AUTOTRACE ON sql> select * from containers(v_t1) where con_id =4; C1 C3 CON_ID ---------- -- ---------- 2 BB 4 Execution Plan ---------------------------------------------------------- Plan hash value: 1051007651 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 400 | 12000 | 1 (100)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 400 | 12000 | 1 (100)| 00:00:01 | | | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 292 recursive calls 0 db block gets 332 consistent gets 0 physical reads 0 redo size 674 bytes sent via sql*Net to client 608 bytes received via sql*Net from client 2 sql*Net roundtrips to/from client 24 sorts (memory) 0 sorts (disk) 1 rows processed sql>
