oracle18c如何创建hr用户,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