SQL> select * from v$version;
BANNER                                                                                                                 CON_ID
--------------------------------------------------------------------------------                       ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production      0
PL/SQL Release 12.1.0.2.0 - Production                                                                 0
CORE    12.1.0.2.0      Production                                                                           0
TNS for Solaris: Version 12.1.0.2.0 - Production                                                    0
NLSRTL Version 12.1.0.2.0 - Production                                                                0

SQL> show pdbs;
    CON_ID CON_NAME             OPEN MODE RESTRICTED
---------- ------------------------------ ----------            ----------
         2 PDB$SEED                       READ ONLY   NO
         3 PDB1                                 READ WRITE NO
         4 PDB2                                 READ WRITE NO

查看listener的状态，可以看到pdb1和pdb2都分别各有一个服务。
$ lsnrctl status

LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 30-MAR-2015 06:48:50
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sca-t4-1-14-ld1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
Start Date                30-MAR-2015 06:40:39
Uptime                    0 days 0 hr. 8 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /export/home/dbusr/app/dbusr/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /export/home/dbusr/app/dbusr/diag/tnslsnr/sca-t4-1-14-ld1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sca-t4-1-14-ld1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=sca-t4-1-14-ld1)(PORT=5500))(Security=(my_wallet_directory=/export/home/dbusr/app/dbusr/admin/oste/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "oste" has 2 instance(s).
Instance "oste", status UNKNOWN, has 1 handler(s) for this service...
Instance "oste", status READY, has 1 handler(s) for this service...
Service "osteXDB" has 1 instance(s).
Instance "oste", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "oste", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "oste", status READY, has 1 handler(s) for this service...
The command completed successfully

在cdb中只能创建全局用户(c##开头), 默认container=all,会在cdb和所有的pdb中创建该用户, 在pdb中只能创建本地用户。
用户授权默认情况下是只会给当前container, 在cdb中也可以指定container=all, 对所有open的pdb中的该用户进行授权。但是pdb中的全局用户需要另外授权才能够在pdb中访问。
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> create user george identified by george;
create user george identified by george
            *
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> create user c##george identified by george;
User created.
SQL> grant connect,resource to c##george;
Grant succeeded.

SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS where username='C##GEORGE';
USERNAME                 CON_ID    USER_ID
-------------------- ---------- ----------
C##GEORGE                     4        104
C##GEORGE                     3        105
C##GEORGE                     1        103

以下是在pdb中创建用户。
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB1

SQL> create user soe identified by soe;
User created.
SQL> grant connect, resource to soe;
Grant succeeded.

SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS where username='SOE';
USERNAME      CON_ID    USER_ID
-------------------- ----------      ----------
SOE                    3                 106

ITPub博客

Oracle 12c中创建用户

最新文章