Oracle12c连接pdb并创建用户

一、连接数据库（pdb可插拔数据库）

#使用sqlplu / as sysdba登录数据库主容器
[tian][oracle@junzi ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 4 11:21:02 2021

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

Connected to an idle instance.
#启动数据库主容器实例
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size		    		8794848 bytes
Variable Size		 	 1090522400 bytes
Database Buffers	 	 1040187392 bytes
Redo Buffers		    	7979008 bytes
Database mounted.
Database opened.
#查看数据库pdb(可插拔数据库)
SQL> show pdbs

   CON_ID CON_NAME			  	OPEN MODE  RESTRICTED
--------- -------------------------------- -----------
	 	2 PDB$SEED			  	READ ONLY  NO
	 	3 PDB01			  		MOUNTED
SQL> alter session set container=pdb01;

Session altered.
#启动pdb，(或使用： alter pluggable database PDB01 open;)
SQL> startup
Pluggable Database opened.
SQL> show pdbs

   CON_ID CON_NAME			  	OPEN MODE  RESTRICTED
--------- -------------------------------- -----------
	 	3 PDB01			  		READ WRITE NO
SQL> 

二、创建用户并授予基本权限

#创建用户：xiaohong 密码：123456
SQL> CREATE USER xiaohong IDENTIFIED BY 123456;

User created.
#授予基本连接和资源权限
SQL> grant connect,resource to xiaohong;

Grant succeeded.

SQL> 

三、用户登录

1、查看数据库监听状态

[tian][oracle@junzi ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 04-JUN-2021 13:17:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=junzi)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                04-JUN-2021 10:32:08
Uptime                    0 days 2 hr. 45 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/junzi/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=junzi)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "c0f270c9739047a8e0537930a8c0e6e9" has 1 instance(s).
  Instance "tian", status READY, has 1 handler(s) for this service...
Service "pdb01" has 1 instance(s).
  Instance "tian", status READY, has 1 handler(s) for this service...
Service "tian" has 1 instance(s).
  Instance "tian", status READY, has 1 handler(s) for this service...
Service "tianXDB" has 1 instance(s).
  Instance "tian", status READY, has 1 handler(s) for this service...
The command completed successfully
[tian][oracle@junzi /u01/app/oracle/product/12.2.0/network/admin]$ 

#查看pdb状态
SQL> show pdbs

   CON_ID CON_NAME			  	OPEN MODE  RESTRICTED
--------- -------------------------------- -----------
	 	3 PDB01			  		READ WRITE NO
#用户登录
SQL> conn xiaohong/123456
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> 

不能使用connect直接登录在pdb中创建的普通用户，普通用户登录需指定pdb

1、使用sqlplus指定pdb用户登录
语法：sqlplus username/password@IP:port/pdb_name
若密码中含有“@”，“$”中等特殊字符，则将用密码需加上双引号:sqlplus 'username/"password"'

[tian][oracle@junzi ~]$ sqlplus xiaohong/123456@192.168.48.121:1521/pdb01

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 4 11:55:31 2021

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 user
USER is "XIAOHONG"
SQL> 
#XIAOHONG用户已经登录到pdb01容器数据库

2、使用connect指定pdb用户登录

1、配置tns
tns文件路径：$ORACLE_HOME/network/admin/tnsnames.ora
添加以下内容：

PDB01 =															#pdb服务名(可自定义)，connect直接@这个名字
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = junzi)(PORT = 1521))		#连接方式，主机名，端口号
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb01)									#数据库中pdb服务名
    )
  )

语法：connect username/password@pdb_name

#使用sysdba用户登录数据库
[tian][oracle@junzi /u01/app/oracle/product/12.2.0/network/admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 4 13:38:39 2021

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
#使用xiaohong用户登录pdb01可插拔数据库
SQL> conn xiaohong/123456@PDB01
Connected.
SQL> 

连接成功，至此结束，有什么问题欢迎留言。