Oracle18c创建不带C##的用户

时间:2021-09-25 16:44:51

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