Oracle Database 12c 建立帳號

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
------------------------------
CDBROOT

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 CDBROOT

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

SQL> select pdb from vservices;

PDB
------------------------------
PDBORCL
CDBROOT
CDBROOT
CDBROOT
CDBROOT

若要建立一個共用(common)帳號在 CDBROOT ,則必需在帳號前加入 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 OPENCLOSE; 來開啟 pluggable databases (PDBs)

SQL> SHOW PDBS;

SQL> show pdbs;

CON_ID CON_NAME              OPEN MODE RESTRICTED
---------- ------------------------ ----------------- ----------
   2        PDBSEED              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       PDBSEED              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 CDBROOT"
// *Cause: An attempt was made to create a local user or role in CDBROOT.
// *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