Oracle 12c：用户创建相关的内容（ORA-65096: invalid common user or role name）
在使用12c的时候，创建用户可能你也会遇到我当前的这个问题：
|
1 2 3 4 5 6 7 8 |
SQL> create user adamhuan identified by oracle; create user adamhuan identified by oracle * ERROR at line 1: ORA-65096: invalid common user or role name SQL> |
先看看当前库里面有的用户：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
SQL> col username for a32 SQL> set linesize 900 SQL> select user_id,username,account_status,oracle_maintained,to_char(created,'YYYY-MM-DD HH24:mi:ss') "Created",to_char(last_login,'YYYY-MM-DD HH24:mi:ss') "Last Login" from dba_users; USER_ID USERNAME ACCOUNT_STATUS O Created Last Login ---------- -------------------------------- -------------------------------- - ------------------- ------------------- 36 ORACLE_OCM EXPIRED & LOCKED Y 2014-07-07 05:44:37 70 OJVMSYS EXPIRED & LOCKED Y 2014-07-07 06:07:27 2147483619 SYSKM EXPIRED & LOCKED Y 2014-07-07 05:39:14 2147483638 XS$NULL EXPIRED & LOCKED Y 2014-07-07 05:44:03 61 GSMCATUSER EXPIRED & LOCKED Y 2014-07-07 05:56:36 85 MDDATA EXPIRED & LOCKED Y 2014-07-07 06:20:54 2147483617 SYSBACKUP EXPIRED & LOCKED Y 2014-07-07 05:39:13 23 DIP EXPIRED & LOCKED Y 2014-07-07 05:43:05 2147483618 SYSDG EXPIRED & LOCKED Y 2014-07-07 05:39:14 95 APEX_PUBLIC_USER EXPIRED & LOCKED Y 2014-07-07 06:29:17 90 SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED Y 2014-07-07 06:25:01 USER_ID USERNAME ACCOUNT_STATUS O Created Last Login ---------- -------------------------------- -------------------------------- - ------------------- ------------------- 87 SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED Y 2014-07-07 06:24:57 22 GSMUSER EXPIRED & LOCKED Y 2014-07-07 05:42:58 7 AUDSYS EXPIRED & LOCKED Y 2014-07-07 05:39:13 94 FLOWS_FILES EXPIRED & LOCKED Y 2014-07-07 06:29:17 99 DVF EXPIRED & LOCKED Y 2014-07-07 06:51:55 79 MDSYS EXPIRED & LOCKED Y 2014-07-07 06:12:11 75 ORDSYS EXPIRED & LOCKED Y 2014-07-07 06:12:11 48 DBSNMP EXPIRED & LOCKED Y 2014-07-07 05:53:06 62 WMSYS EXPIRED & LOCKED Y 2014-07-07 06:05:43 98 APEX_040200 EXPIRED & LOCKED Y 2014-07-07 06:29:17 49 APPQOSSYS EXPIRED & LOCKED Y 2014-07-07 05:53:07 USER_ID USERNAME ACCOUNT_STATUS O Created Last Login ---------- -------------------------------- -------------------------------- - ------------------- ------------------- 21 GSMADMIN_INTERNAL EXPIRED & LOCKED Y 2014-07-07 05:42:58 76 ORDDATA EXPIRED & LOCKED Y 2014-07-07 06:12:11 73 CTXSYS EXPIRED & LOCKED Y 2014-07-07 06:11:11 51 ANONYMOUS EXPIRED & LOCKED Y 2014-07-07 05:53:17 50 XDB EXPIRED & LOCKED Y 2014-07-07 05:53:17 77 ORDPLUGINS EXPIRED & LOCKED Y 2014-07-07 06:12:11 1279990 DVSYS EXPIRED & LOCKED Y 2014-07-07 06:51:55 78 SI_INFORMTN_SCHEMA EXPIRED & LOCKED Y 2014-07-07 06:12:11 82 OLAPSYS EXPIRED & LOCKED Y 2014-07-07 06:20:30 92 LBACSYS EXPIRED & LOCKED Y 2014-07-07 06:29:01 13 OUTLN EXPIRED & LOCKED Y 2014-07-07 05:39:15 USER_ID USERNAME ACCOUNT_STATUS O Created Last Login ---------- -------------------------------- -------------------------------- - ------------------- ------------------- 8 SYSTEM OPEN Y 2014-07-07 05:39:13 0 SYS OPEN Y 2014-07-07 05:39:13 35 rows selected. SQL> |
在12c中由于加入了多租户的概念，引入了Contain DB与Pluggable DB，所以对于ContainDB中创建的用户，称为公有用户。
公有用户的命名必须以“C##”打头：
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> create user c##adamhuan identified by oracle; User created. SQL> select user_id,username,account_status,oracle_maintained,to_char(created,'YYYY-MM-DD HH24:mi:ss') "Created",to_char(last_login,'YYYY-MM-DD HH24:mi:ss') "Last Login" from dba_users where username like '%ADAMHUAN'; USER_ID USERNAME ACCOUNT_STATUS O Created Last Login ---------- -------------------------------- -------------------------------- - ------------------- ------------------- 102 C##ADAMHUAN OPEN N 2015-09-07 16:15:58 SQL> |
而对于PDB中创建的用户，则和以前的用户管理一样的。（这一部分的样例在下文中会再次提到）
查询CDB的信息：
|
1 2 3 4 5 6 7 |
SQL> select name,cdb from v$database; NAME CDB --------- --- ORCL YES SQL> |
查询PDB的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> col pdb_name for a30 SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs; PDB_ID PDB_NAME DBID STATUS CREATION_SCN ---------- ------------------------------ ---------- --------- ------------ 3 PDB_ORCL 787125692 NORMAL 1604169 2 PDB$SEED 2457082255 NORMAL 1594414 SQL> select con_id,dbid,name,open_mode from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 2 2457082255 PDB$SEED READ ONLY 3 787125692 PDB_ORCL MOUNTED SQL> |
判断当前是否位于CDB或者PDB：
|
1 2 3 4 5 6 |
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> |
当前位于CDB。
切换到PDB：
|
1 2 3 4 5 6 7 8 9 10 |
SQL> alter session set container=PDB_ORCL; Session altered. SQL> show con_name CON_NAME ------------------------------ PDB_ORCL SQL> |
打开PDB：
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> alter pluggable database pdb_orcl open; Pluggable database altered. SQL> select con_id,dbid,name,open_mode from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 3 787125692 PDB_ORCL READ WRITE SQL> |
PDB – 创建用户：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> show con_name CON_NAME ------------------------------ PDB_ORCL SQL> SQL> create user alienwalker identified by oracle; User created. SQL> select user_id,username,account_status,oracle_maintained,to_char(created,'YYYY-MM-DD HH24:mi:ss') "Created",to_char(last_login,'YYYY-MM-DD HH24:mi:ss') "Last Login" from dba_users where username like '%ALIENWALKER%'; USER_ID USERNAME ACCOUNT_STATUS O Created Last Login ---------- ------------------------------ -------------------------------- - ------------------- ------------------- 111 ALIENWALKER OPEN N 2015-09-07 16:30:43 SQL> |
可以看到，这里创建用户就和12c之前的版本中创建用户是一样的了。
————————————————————————————————————
Done。
