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

文章標籤
創作者介紹
MIS

MISTECH 技術手抄本

MIS 發表在 痞客邦 PIXNET 留言(0) 人氣()