oracle如何查cdb,oracle如何查看视图的sql

Oracle19c基本操作

0、查看db

10多年专注成都网站制作，成都定制网站，个人网站制作服务，为大家分享网站制作知识、方案，网站设计流程、步骤,成功服务上千家企业。为您提供网站建设,网站制作,网页设计及定制高端网站建设服务,专注于成都定制网站,高端网页制作,对木屋等多个行业，拥有丰富的网站运维经验。

show con_name

show pdbs

1、cdb切换至pdb

alter session set container=pdb;

2、pdb切换至cdb

alter session set container=cdb$root;

3、pdb更改字符集（cdb的字符集必须为al32utf8）

alter system enable restricted session; （恢复操作为alter system disable...）

alter database character set internal_use zhs16gbk;

注：使用低版本的client在不同字符集的pdb之间切换会报错：ora-24964

4、实例启动后自动打开所有pdb

CREATE OR REPLACE TRIGGER open_pdbs AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END open_pdbs;

5、pdb克隆

create pluggable database pdb2 from pdb1 file_name_convert=('pdb1path','pdb2path');

oracle pdb库怎么访问cdb用户的对象

Oracle 12C引入了CDB与PDB的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。CDB全称为Container Database，中文翻译为数据库容器，PDB全称为Pluggable Database，即可插拔数据库。在ORACLE 12C之前，实例与数据库是一对一或多对一关系（RAC）：即一个实例只能与一个数据库相关联，数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后，实例与数据库可以是一对多的关系。下面是官方文档关于CDB与PDB的关系图。

clip_image002

其实大家如果对SQL SERVER比较熟悉的话，这种CDB与PDB是不是感觉和SQL SERVER的单实例多数据库架构是一回事呢。像PDB$SEED可以看成是master、msdb等系统数据库，PDBS可以看成用户创建的数据库。而可插拔的概念与SQL SERVER中的用户数据库的分离、附加其实就是那么一回事。看来ORACLE也“抄袭”了一把SQL SERVER的概念，只是改头换面的包装了一番。

CDB组件（Components of a CDB）

一个CDB数据库容器包含了下面一些组件：

ROOT组件

ROOT又叫CDB$ROOT, 存储着ORACLE提供的元数据和Common User,元数据的一个例子是ORACLE提供的PL/SQL包的源代码，Common User 是指在每个容器中都存在的用户。

SEED组件

Seed又叫PDB$SEED,这个是你创建PDBS数据库的模板，你不能在Seed中添加或修改一个对象。一个CDB中有且只能有一个Seed. 这个感念，个人感觉非常类似SQL SERVER中的model数据库。

PDBS

CDB中可以有一个或多个PDBS，PDBS向后兼容，可以像以前在数据库中那样操作PDBS，这里指大多数常规操作。

这些组件中的每一个都可以被称为一个容器。因此，ROOT(根)是一个容器，Seed(种子)是一个容器，每个PDB是一个容器。每个容器在CDB中都有一个独一无二的的ID和名称。

1）连接到CDB数据库

连接到CDB数据库容器非常简单，跟以前连接数据库是一样的

复制代码

[oracle@get-orasvr02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Oct 20 23:41:36 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL

[oracle@get-orasvr02 ~]$ sqlplus sys/password as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Oct 20 23:43:17 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL

复制代码

2）查看数据库是否为CDB

复制代码

SQL select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;

NAME Multitenant Option OPEN_MODE CON_ID

--------- ----------------------------- -------------------- ----------

EPPS Multitenant Option enabled READ WRITE 0

复制代码

YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)

3）查看当前容器（Container）

3.1

复制代码

SQL show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL

3.2

SQL select sys_context('userenv', 'con_name') "Container DB" from dual;

Container DB

----------------------------------------------------

CDB$ROOT

SQL

复制代码

4）查看CDB容器中的PDBS信息

查看CDB中有多少个pluggable database

复制代码

SQL select con_id, dbid, guid, name , open_mode from v$pdbs;

CON_ID DBID GUID NAME OPEN_MODE

---------- ---------- -------------------------------- ------------------------------ ----------

2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY

3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS MOUNTED

SQL

复制代码

5）启动PDB数据库

方式1：

复制代码

SQL alter pluggable database PDBEPPS open;

Pluggable database altered.

SQL select con_id, dbid, guid, name , open_mode from v$pdbs;

CON_ID DBID GUID NAME OPEN_MODE

---------- ---------- -------------------------------- ------------------------------ ----------

2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY

3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS READ WRITE

复制代码

方式2：

复制代码

SQL alter session set container=PDBEPPS;

Session altered.

SQL startup

Pluggable Database opened.

SQL

复制代码

6）关闭PDB数据库

SQL alter pluggable database PDBEPPS close;

Pluggable database altered.

SQL select con_id, dbid, guid, name , open_mode from v$pdbs;

CON_ID DBID GUID NAME OPEN_MODE

---------- ---------- -------------------------------- ------------------------------ ----------

2 4071321146 E89E8DA2866E3157E043DE07A8C09238 PDB$SEED READ ONLY

3 1930201447 E89E9418B882350CE043DE07A8C092B6 PDBEPPS MOUNTED

SQL

7）在容器间切换

SQL alter session set container=PDBEPPS;

Session altered.

SQL show con_name;

CON_NAME

------------------------------

PDBEPPS

SQL

SQL alter session set container=CDB$ROOT;

Session altered.

SQL show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL

Oracle 12c 怎么连接到 CDB 和 PDB

1 连接到CDB

和普通实例一样的连接。 指定ORACLE_SID 以后可以使用OS认证，也可以使用密码进行连接。

[oracle@Ora12c /]$ echo $ORACLE_SID

cndba

[oracle@Ora12c /]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production onMon Apr 28 11:33:43 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options

SQL conn system/oracle

Connected.

SQL

--查看CDB中可用的service：

SQL COLUMN name FORMAT A30

SQL SELECT name,pdb

2 FROM v$services

3 ORDER BY name;

NAME PDB

------------------------------------------------------------

SYS$BACKGROUND CDB$ROOT

SYS$USERS CDB$ROOT

cndba CDB$ROOT

cndbaXDB CDB$ROOT

pcndba2 PCNDBA2

pdbcndba PDBCNDBA

6 rows selected.

--通过lsnrctl 也可以判断：

[oracle@Ora12c /]$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.1.0 -Production on 28-APR-2014 11:35:31

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

Services Summary...

Service "cndba" has 1instance(s).

Instance "cndba", status READY, has 1 handler(s) for thisservice...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "cndbaXDB" has 1instance(s).

Instance "cndba", status READY, has 1 handler(s) for thisservice...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER machine: Ora12c, pid: 10085

(ADDRESS=(PROTOCOL=tcp)(HOST=Ora12c)(PORT=14696))

Service "pcndba2" has 1instance(s).

Instance "cndba", status READY, has 1 handler(s) for thisservice...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "pdbcndba" has 1instance(s).

Instance "cndba", status READY, has 1 handler(s) for thisservice...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

The command completed successfully

[oracle@Ora12c /]$

通过这些service，就可以远程连接CDB。

--EZCONNECT

C:\Users\Davesqlplussystem/oracle@192.168.1.10:1521/cndba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:36:48 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:

Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options

SQL

--通过TNSNAMES.ORA连接：

在tnsnames.ora 中配置如下：

cndba =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cndba)

)

)

--连接：

C:\Users\Davesqlplussystem/oracle@cndba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:40:01 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:

Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options

SQL

2 在不同Container database中切换

在12c的架构中，因为有CDB和 PDB的存在，所以会有很多不同的container，所以在连接到不同的container时，就需要进行切换。

--查看当前的container：

SQL show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

CDB$ROOT

SQL set lin 140

SQL select con_id, dbid,guid, name , open_mode from v$pdbs;

CON_ID DBID GUID NAME OPEN_MODE

---------- ------------------------------------------ ---------- ----------

2 4088301206 F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEED READ ONLY

3 426143573F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA READ WRITE

4 1231796139 F812DE1B6A8F363AE0430A01A8C0C759 PCNDBA2 READ WRITE

--切换container：

SQL alter session setcontainer=pcndba2;

Session altered.

SQL show con_name

CON_NAME

------------------------------

PCNDBA2

3 连接到PDB

--EZCONNECT:

C:\Users\Davesqlplussystem/oracle@192.168.1.10:1521/pcndba2

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:54:30 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:

Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options

SQL

--TNSNAMES.ora

在tnsnames.ora 中添加如下内容：

pcndba =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pcndba2)

)

)

C:\Users\Davesqlplussystem/oracle@pcndba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:55:50 2014

Copyright (c) 1982, 2010, Oracle. All rights reserved.

连接到:

Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options

SQL

如何配置Hibernate连接oracle12c 的PDB和CDB数据库

启动根容器：

[oracle@eric ~]$ export ORACLE_SID=cup

[oracle@eric ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 16:00:06 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL startup -----不会直接启动所有可插拔数据库，如需启动所有可插拔数据库，执行命令：alter pluggable database all open

ORACLE instance started.

Total System Global Area 767557632 bytes

Fixed Size 2929112 bytes

Variable Size 574623272 bytes

Database Buffers 184549376 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

停止根容器：

SQL shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

查看是否创建了CDB，如果有显示名字:

SQL select name,cdb from v$database;

NAME CDB

--------- ---

CUP YES

SQL show parameter service;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

service_names string cup

查看容器名字，其中有3个容器：根容器、种子容器和自己创建的容器:

SQL select con_id,name from v$containers;

CON_ID NAME

---------- ------------------------------

1 CDB$ROOT ---根容器

2 PDB$SEED ---种子容器，只可读

3 TEA ---自己创建的可插拔数据库

SQL select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/oracle/app/oradata/CUP/datafile/o1_mf_system_bch07kvz_.dbf

/oracle/app/oradata/CUP/datafile/o1_mf_sysaux_bch020oo_.dbf

/oracle/app/oradata/CUP/datafile/o1_mf_undotbs1_bch0d2on_.dbf

/oracle/app/oradata/CUP/datafile/o1_mf_users_bch0d15n_.dbf

SQL select file_name from cdb_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/oracle/app/oradata/CUP/datafile/o1_mf_system_bch07kvz_.dbf

/oracle/app/oradata/CUP/datafile/o1_mf_sysaux_bch020oo_.dbf

/oracle/app/oradata/CUP/datafile/o1_mf_undotbs1_bch0d2on_.dbf

/oracle/app/oradata/CUP/datafile/o1_mf_users_bch0d15n_.dbf

创建公共用户：

SQL create user c##eric identified by gao;

User created.

SQL conn c##eric/gao

ERROR:

ORA-01045: user C##ERIC lacks CREATE SESSION privilege; logon denied ----没有权限，我们可以单独给其赋予权限，也可以给其指定角色。

Warning: You are no longer connected to ORACLE.

SQL conn / as sysdba

Connected.

SQL grant dba to c##eric container=all; ---给其DBA角色，角色范围覆盖所有的容器

Grant succeeded.

SQL conn c##eric/gao

Connected. ---连接成功

切换容器：

首先启动自己创建的容器数据库：

SQL alter pluggable database tea open; ---首先启动PDB数据库

SQL alter session set container = tea;

Session altered.

SQL show con_name ---查看当前所在的容器名

CON_NAME

------------------------------

TEA

关闭可插拔数据库：

SQL alter pluggable database tea close immediate;

Pluggable database altered.