ORA-65096: invalid common user or role name
Oracle Database 12c 因為有 multitenant container database (CDB) 及 pluggable databases (PDBs) 的功能，所以在建立帳號時，就必需要指定。不然會有錯誤訊息：ORA-65096: invalid common user or role name
|
[oracle@oradb12clinux ~]$ echo $ORACLE_SID SQL> show con_name CON_NAME SQL> create user abcd identified by abcd; SQL> create user test identified by test container=current; |
這時候，您要決定建立一個共用(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 |
若要建立一個共用(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
SQL> CREATE USER RMAN_XE IDENTIFIED BY RMAN_PWD |
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 SQL> ALTER PLUGGABLE DATABASE ALL OPEN; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED |
再來試一次吧
|
[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 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 |
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. |