Oracle Database 12c 建立帳號 @ MISTECH 技術手抄本

Oracle Database 12c 因為有 multitenant container database (CDB) 及 pluggable databases (PDBs) 的功能，所以在建立帳號時，就必需要指定。不然會有錯誤訊息：ORA-65096: invalid common user or role name

[oracle@oradb12clinux ~]$ echo $ORACLE_SID
orcl
[oracle@oradb12clinux ~]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user abcd identified by abcd;
create user abcd identified by abcd
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user test identified by test container=current;
create user test identified by test container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

這時候，您要決定建立一個共用(common)帳號於 CDB (a "common" user in the CDB which could be used to manage multiple PDBs )，或是 Local 帳號於 PDB，可以使用 SQL 指令 select pdb from v$services; 來查詢目前有多少服務

SQL> select pdb from v$services;

PDB
------------------------------
PDBORCL
CDB$ROOT
CDB$ROOT
CDB$ROOT
CDB$ROOT

若要建立一個共用(common)帳號在 CDB$ROOT ，則必需在帳號前加入 c##

SQL> create user c##abc identified by abc;

User created.

SQL> drop user c##abc;

User dropped

若要將帳號建在 Local PDB：PDBORCL ，必需先將 session 指定在 container=PDBORCL;

SQL> alter session set container=PDBORCL;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDBORCL

SQL> CREATE USER RMAN_XE IDENTIFIED BY RMAN_PWD
ACCOUNT UNLOCK;
CREATE USER RMAN_XE IDENTIFIED BY RMAN_PWD
*
ERROR at line 1:
ORA-01109: database not open

ORACLE_SID: orcl 資料庫不是明明已 open 了嗎? 奇怪，怎麼還告訴我 database not open，其實它是說 PDB：PDBORCL 沒有open，可以用 SQL 指令：ALTER PLUGGABLE DATABASE PDBORCL OPEN | CLOSE ; 或 ALTER PLUGGABLE DATABASE ALL OPEN|CLOSE; 來開啟 pluggable databases (PDBs)

SQL> SHOW PDBS;

SQL> show pdbs;

CON_ID CON_NAME              OPEN MODE RESTRICTED
---------- ------------------------ ----------------- ----------
   2        PDB$SEED              READ ONLY      NO
   3        PDBORCL              MOUNTED

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME             OPEN MODE RESTRICTED
---------- ------------------------ ---------------- ----------
   2       PDB$SEED              READ ONLY    NO
   3       PDBORCL                READ WRITE NO

再來試一次吧

[oracle@oradb12clinux ~]$ sqlplus / as sysdba

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

SQL> alter session set container=PDBORCL;

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------ ---------------- ----------
3 PDBORCL READ WRITE NO

SQL> CREATE USER RMAN_XE IDENTIFIED BY RMAN_PWD;

User created.

SQL> alter session set container=PDBORCL;

Session altered.

SQL> select username from dba_users where username like '%RMAN%';

USERNAME
--------------------------------------------------------------------------------
RMAN_XE

ORA-65096 的意思:

oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause: An attempt was made to create a common user or role with a name
//          that wass not valid for common users or roles. In addition to
//          the usual rules for user and role names, common user and role
//          names must start with C## or c## and consist only of ASCII
//          characters.
// *Action: Specify a valid common user or role name.

ORA-65049 的意思:

oerr ora 65049
65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
// *Cause: An attempt was made to create a local user or role in CDB$ROOT.
// *Action: If trying to create a common user or role, specify CONTAINER=ALL.

參考：

http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CNCPT89259

http://blog.contractoracle.com/2013/06/oracle-12c-ora-65096.html

http://www.oracle-base.com/articles/12c/multitenant-manage-tablespaces-in-a-cdb-and-pdb-12cr1.php