在Oracle Database 12.2多租户架构中,使用Containers关键字来完成跨pdb查询.

最新推荐文章于 2023-04-25 16:33:54 发布

msdnchina

最新推荐文章于 2023-04-25 16:33:54 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/msdnchina/article/details/68065089

Oracle DB 12c 同时被 2 个专栏收录

62 篇文章

订阅专栏

Oracle DB 12c新特性

39 篇文章

订阅专栏

本文介绍如何在 Oracle Database 12.2 的多租户环境中使用 Containers 关键字进行跨 PDB (Pluggable Database) 查询。通过创建用户、表、视图并设置权限，演示了具体的查询方法。

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