Oracle18c创建不带C##的用户
18c数据库分两种数据库CDB(容器数据库)、PDB(可插拔数据库)
数据库安装完成之后,默认是CDB
创建一个用户,必须要用C##开头,但使用PDB没有这个限制
1. 先查看PDB数据库service
[root@Oracle ~]# lsnrctl status
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 11-SEP-2018 15:15:16
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 10-SEP-2018 19:05:11
Uptime 0 days 20 hr. 10 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/18.3/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/Oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Oracle)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "64a52f53a7683286e053cda9e80aed76" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "75832b41b31036e1e053256ea8c0e61c" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1pdb" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
2. 修改tnsname.ora文件,添加一个pdb的记录,就可以连接pdb
[root@Oracle ~]# su oracle
[oracle@Oracle ~]$ vi /u01/app/oracle/product/18.3/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/18.3/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
cdb1pdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1pdb)
)
)
3. 使用pdb登录
[oracle@Oracle ~]$ sqlplus system/manager@cdb1pdb SQL*Plus: Release 18.0.0.0.0 - Production on Tue Sep 11 15:22:25 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Tue Sep 11 2018 15:09:35 +08:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> create user his identified by 123456; User created. SQL> alter user his account unlock; User altered. SQL> grant create session to his; Grant succeeded. SQL> grant dba to his; Grant succeeded. SQL> grant create user,drop user,alter user,create any view,drop any view,exp_full_database,imp_full_database,dba,connect,resource,create session to his; Grant succeeded.
4. PLSQL链接
IP: 192.168.110.37 端口: 1521 服务名: cdb1pdb 用户名: his 密码: 123456