在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 | 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 | |
------------------------------------------------------------------------------------------------------------------------------------
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>