oracle 12c 公用用户创建

1. 使用sqlplus登录可插拔数据库

[[oracle@node1 ~]$ sqlplus / as sysdba

 

2. 通过con_name查看CDB包含的pdb信息。

SQL> show con_name pdbs;

 

CON_NAME

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

CDB$ROOT

 

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ WRITE NO

3 QXY11PDB READ WRITE NO

4 PDB02 READ WRITE NO

SQL>

3. 创建公用用户qxy, 需要使用C##或者c##作为该用户名的开头。

SQL> create user c##qxy identified by tiger;

User created.

SQL>

 

如果使用传统创建用户的方法会报错

SQL> create user qxy identified by tiger;

create user qxy identified by tiger

*

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL>

4. 授权

SQL> grant dba to c##qxy identified by tiger container = all;

 

5. 下面使用公用用户分别登录CDB、PDB看看是否通用。 登录PDB使用了tnsnames中已经配置好的链接字符串@pdb02

[oracle@node1 admin]$ sqlplus c##qxy/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 9 13:01:07 2018

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

Connected to

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL>

--------连接pdb

 [oracle@node1 admin]$ sqlplus c##qxy/tiger@pdb02

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 9 13:04:04 2018

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

Last Successful login time: Mon Apr 09 2018 13:03:46 +08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

 

CON_NAME

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

PDB02

SQL>

链接字符串如下：

pdb02 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb02.localdomain)

)

)

)

6. 在PDB中创建用户和创建普通用户就没有什么区别了

SQL> show con_name

CON_NAME

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

PDB02

SQL>

SQL> create user scott identified by tiger ;

User created.

SQL> grant resource,connect to scott;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@node1 admin]$ sqlplus scott/tiger@pdb02

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 9 13:06:55 2018

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME

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

PDB02

SQL>

7. CDB和PDB之间的切换方式

SQL > alter session set container = pdb02;

SQL > alter session set container = cdb$root;