ORA-65096 invalid common user or role name
来源：互联网 发布：mac给ios手机装 编辑：程序博客网 时间：2024/05/16 08:01
环境：win7 64bit,oracle 12c(12.1.0.1.0)
转自:http://blog.csdn.net/liou825/article/details/10054589
先说基本用法：
先按11G之前进行
conn / as sysdba;
create user test identifed by test;
ORA-65096: 公用用户名或角色名无效.
查官方文档得知“试图创建一个通用用户，必需要用C##或者c##开头”，这时候心里会有疑问，什么是common user？不管先建成功了再说
create C##user test identifed by test;
创建成功
SQL>show con_name;
CON_NAME
------------------------------
CDB$ROOTselectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4066409480 PDB$SEED READ ONLY
3 2270995695 PDBORCL MOUNTED
SQL>alter session set container=PDBORCL;
这时再用create user test identifed by test;建立用户就可以了。
以下文章原文：http://www.xifenfei.com/4432.html
CDB和PDB是ORACLE 12C一个很亮的新特性,由于他们的引入导致传统的ORACLE数据库管理理念不少发生了改变,这里列举了部分最基本的cdb和pdb管理方式
cdb和pdb关系图
ORACLE 12C版本
SQL>select*fromv$version;BANNER CON_ID-------------------------------------------------------------------------------- ----------OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0PL/SQL Release 12.1.0.1.0 - Production 0CORE 12.1.0.1.0 Production 0TNSforLinux: Version 12.1.0.1.0 - Production 0NLSRTL Version 12.1.0.1.0 - Production 0启动关闭pdb
SQL> startupORACLE instance started.Total System GlobalArea 597098496 bytesFixedSize 2291072 bytesVariableSize 272632448 bytesDatabaseBuffers 314572800 bytesRedo Buffers 7602176 bytesDatabasemounted.Databaseopened.SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4048821679 PDB$SEED READONLY 3 3313918585 PDB1 MOUNTED 4 3872456618 PDB2 MOUNTEDSQL>alterPLUGGABLEdatabasepdb1open;Pluggabledatabasealtered.SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4048821679 PDB$SEED READONLY 3 3313918585 PDB1 READWRITE 4 3872456618 PDB2 MOUNTEDSQL>alterPLUGGABLEdatabasepdb1close;Pluggabledatabasealtered.SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4048821679 PDB$SEED READONLY 3 3313918585 PDB1 MOUNTED 4 3872456618 PDB2 MOUNTEDSQL>alterPLUGGABLEdatabaseallopen;Pluggabledatabasealtered.SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4048821679 PDB$SEED READONLY 3 3313918585 PDB1 READWRITE 4 3872456618 PDB2 READWRITESQL>alterPLUGGABLEdatabaseallclose;Pluggabledatabasealtered.SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4048821679 PDB$SEED READONLY 3 3313918585 PDB1 MOUNTED 4 3872456618 PDB2 MOUNTEDSQL>altersessionsetcontainer=pdb1;Session altered.SQL> startupPluggableDatabaseopened.SQL> selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 3 3313918585 PDB1 READWRITEpdb的管理可以在cdb中进行也可以在pdb中进行,如果是cdb中进行,需要PLUGGABLE关键字,如果是pdb中直接和普通数据库一样
登录pdb
[oracle@xifenfei ~]$ lsnrctl statusLSNRCTLforLinux: Version 12.1.0.1.0 - Production on12-MAY-2013 08:07:02Copyright (c) 1991, 2013, Oracle. Allrights reserved.Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))STATUSofthe LISTENER------------------------Alias LISTENERVersion TNSLSNR forLinux: Version 12.1.0.1.0 - ProductionStartDate 11-MAY-2013 18:30:54Uptime 0 days 13 hr. 36 min. 8 secTraceLevel offSecurity ON:LocalOS AuthenticationSNMP OFFListener Parameter File /u01/app/grid/product/12.1/network/admin/listener.oraListener Log File /u01/app/grid/diag/tnslsnr/xifenfei/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xifenfei)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/12.1/db_1/admin/cdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))Services Summary...Service"+ASM"has 1 instance(s). Instance"+ASM", status READY, has 1 handler(s) forthis service...Service"cdb"has 1 instance(s). Instance"cdb", status READY, has 1 handler(s) forthis service...Service"cdbXDB"has 1 instance(s). Instance"cdb", status READY, has 1 handler(s) forthis service...Service"pdb1"has 1 instance(s). Instance"cdb", status READY, has 1 handler(s) forthis service...Service"pdb2"has 1 instance(s). Instance"cdb", status READY, has 1 handler(s) forthis service...The command completed successfully[oracle@xifenfei ~]$ tnsping pdb1TNS Ping Utility forLinux: Version 12.1.0.1.0 - Production on12-MAY-2013 08:07:09Copyright (c) 1997, 2013, Oracle. Allrights reserved.Used parameter files:Used TNSNAMES adapter toresolve the aliasAttemptingtocontact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))OK (20 msec)[oracle@xifenfei ~]$ sqlplus sys/xifenfei@pdb1 assysdbaSQL*Plus: Release 12.1.0.1.0 Production onSun May 12 08:08:02 2013Copyright (c) 1982, 2013, Oracle. Allrights reserved.Connectedto:OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWiththe Partitioning, Automatic Storage Management, OLAP, Advanced AnalyticsandRealApplication Testing optionsSQL> show con_name;CON_NAME------------------------------PDB1[oracle@xifenfei ~]$ sqlplus / assysdbaSQL*Plus: Release 12.1.0.1.0 Production onSun May 12 08:09:14 2013Copyright (c) 1982, 2013, Oracle. Allrights reserved.Connectedto:OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWiththe Partitioning, Automatic Storage Management, OLAP, Advanced AnalyticsandRealApplication Testing optionsSQL>altersessionsetcontainer=pdb1;Session altered.SQL> show con_name;CON_NAME------------------------------PDB1pdb可以通过alter session container进入也可以直接通过tns方式登录
创建用户
SQL> show con_name;CON_NAME------------------------------CDB$ROOTSQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 2 4048821679 PDB$SEED READONLY 3 3313918585 PDB1 READWRITE 4 3872456618 PDB2 MOUNTEDSQL>createuserxff identified byxifenfei;createuserxff identified byxifenfei *ERRORatline 1:ORA-65096: invalid common userorrole nameSQL> !oerr ora 6509665096, 00000, "invalid common user or role name"// *Cause: An attempt was made tocreatea common userorrole withaname// that wass notvalidforcommon users orroles. Inadditionto// the usual rules foruserand role names, common userandrole // names must start withC##orc##andconsistonlyofASCII // characters.// *Action: Specify a valid common userorrole name.//SQL>createuserc##xff identified byxifenfei;Usercreated.SQL>SELECTUSERNAME,CON_ID,USER_IDFROMCDB_USERSWHEREUSERNAME='C##XFF';USERNAME CON_ID USER_ID---------- ---------- ----------C##XFF 1 103C##XFF 3 104SQL>altersessionsetcontainer=pdb1;Session altered.SQL> show con_nameCON_NAME------------------------------PDB1SQL>createuserxff identified byxifenfei;Usercreated.SQL>createuserc##abc identified byxifenfei;createuserc##abc identified byxifenfei *ERRORatline 1:ORA-65094: invalid localuseror role name创建用户默认的是container=all,在cdb中只能创建全局用户(c##开头),会在cdb和所有的pdb中创建该用户(但是pdb中的全局用户需要另外授权才能够在pdb中访问)。在pdb中只能创建的用户为本地用户
用户授权
SQL>grantconnectto c##xff;Grantsucceeded.SQL>selectGRANTEE,con_idfromcdb_ROLE_PRIVSwhereGRANTED_ROLE='CONNECT'ANDGRANTEE='C##XFF';GRANTEE CON_ID------------------------------ ----------C##XFF 1SQL>grantresourcetoc##xff container=all;Grantsucceeded.SQL> selectGRANTEE,con_idfromcdb_ROLE_PRIVSwhereGRANTED_ROLE='RESOURCE'AND GRANTEE='C##XFF';GRANTEE CON_ID------------------------------ ----------C##XFF 1C##XFF 3用户授权默认情况下是只会给当前container,在cdb中也可以指定container=all,对所有open的pdb且存在该用户都进行授权
修改参数
SQL>altersystemsetopen_cursors=500 container=all;System altered.SQL> conn sys/xifenfei@pdb1 assysdbaConnected.SQL> show parameter open_cursors;NAME TYPE VALUE------------------------------------ ----------- ------------------------------open_cursors integer 500SQL> altersystemsetopen_cursors=100; altersystemsetopen_cursors=100*ERRORatline 1:ORA-01219:databaseorpluggable databasenotopen: queries allowed onfixedtablesorviewsonlySQL>alterdatabaseopen;Databasealtered.SQL>altersystemsetopen_cursors=100;System altered.SQL> show parameter open_cursors;NAME TYPE VALUE------------------------------------ ----------- ------------------------------open_cursors integer 100SQL> conn / assysdbaConnected.SQL> show parameter open_cursors;NAME TYPE VALUE------------------------------------ ----------- ------------------------------open_cursors integer 500这里可以看到在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义
参考:
http://www.tuicool.com/articles/V3UJZfA
http://www.xifenfei.com/2013/05/oracle-12c-pdb-%E7%AE%A1%E7%90%86%E4%BB%8B%E7%BB%8D1.html
0 0
- ORA-65096: invalid common user or role name
- ORA-65096 invalid common user or role name
- Oracle 12c创建用户是出现“ORA-65096: invalid common user or role name”的错误 - CalvinR
- oracle12创建用户错误ORA-65096: 公用用户名或角色名无效 invalid common user or role name
- Oracle 12c创建用户时出现“ORA-65096: invalid common user or role name”的错误
- Oracle 12c创建用户是出现“ORA-65096: invalid common user or role name”的错误
- Oracle 12c创建用户是出现“ORA-65096: invalid common user or role name”的错误 - CalvinR
- oracle12创建用户错误ORA-65096: 公用用户名或角色名无效 invalid common user or role name
- oracle12创建用户错误ORA-65096: 公用用户名或角色名无效 invalid common user or role name
- oracle12 新建用户报错 invalid common user or role name
- oracle12c创建用户报错： invalid common user or role name
- 在 12cR1 数据库中创建测试用户是报 ORA-65096 错误(2c: ORA-65049: creation of local user or role is not allowed in C)
- ORA-20001: Statistics Advisor: Invalid task name for the current user
- Unable to Sign In An invalid User Name or Password was entered解决办法
- 解决异常-ORA-01747 invalid user.table.column, table.column, or column specification
- 异常-ORA-01747: invalid user.table.column, table.column, or column specification
- Oracle ORA-01747: invalid user.table.column, table.column, or column specification
- ORA-01747: invalid user.table.column, table.column, or column specification
- 特征点检测效果评估（matlab代码）
- 杭电acm--2050
- 操作JSONObject中的某一个bean
- 白话经典算法系列之十五 “一步千里”之数组找数
- 第13章 线程安全与锁优化
- ORA-65096 invalid common user or role name
- 用第三方程序打开自己应用里的文档
- 策略模式（Strategy）
- C++四种类型转换
- NSOperation封装- 多任务下载
- Map、Set、List、Queue、Stack的特点与用法1
- 将内容追加到文件尾部-采用字符流的形式，将abc.txt中的内容更换为 好好学习，天天向上!
- UIWindow
- 磁盘 分区
