分类: Oracle

Oracle 12c创建用户时出现ORA-65096: invalid common user or role name的错误

CDB和PDB是ORACLE 12C一个新特性,名词解释如下:
ultitenant Environment:多租户环境
CDB(Container Database):数据库容器
PD(Pluggable Database):可插拔数据库

CDB与PDB关系图
cdb.png
COMMON USERS(普通用户):经常建立在CDB层,用户名以C##或c##开头;
LOCAL USERS(本地用户):仅建立在PDB层,建立的时候得指定CONTAINER。

1、查看Oracle 12c的版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
         0

PL/SQL Release 12.2.0.1.0 - Production
         0

CORE    12.2.0.1.0      Production
         0


BANNER
--------------------------------------------------------------------------------
    CON_ID
----------
TNS for Linux: Version 12.2.0.1.0 - Production
         0

NLSRTL Version 12.2.0.1.0 - Production
         0

2、查看工作模式,默认启动以后在CDB下面

SQL> select sys_context ('USERENV', 'CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME') 
----------------------------------------------------------------------------------------------------
 CDB$ROOT

或者使用show con_name查看

SQL>show con_name
 
CON_NAME
------------------------------
CDB$ROOT

3、查看PDB名称

SQL>select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID
---------- ----------
NAME
--------------------------------------------------------------------------------
MOUNTED
----------
         3 1609777056
BRCPDB
READ WRITE

4、打开PDB模式

SQL> alter pluggable database BRCPDB open;
Pluggable database altered.

5、查看容器

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID
---------- ----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
         2 1169339077
PDB$SEED
READ ONLY

         3 1609777056
BRCPDB
READ WRITE

    CON_ID       DBID
---------- ----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------

6、切换容器到pdb模式

SQL>  alter session set container=BRCPDB;
Session altered.

7、查看当前使用容器

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID
---------- ----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
         2 1169339077
PDB$SEED
READ ONLY

         3 1609777056
BRCPDB
READ WRITE

    CON_ID       DBID
---------- ----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------

8、创建用户、授权等

创建表空间
create tablespace brc
datafile '/data/app/oracle/oradata/orcl/brc.dbf'
size 10G
autoextend on;

创建临时表空间
create temporary tablespace brc_temp  
tempfile '/data/app/oracle/oradata/orcl/brc_temp.dbf'
size 5G  
autoextend on;

创建用户
create user ysbrc identified by xxxxxx default tablespace brc temporary tablespace brc_temp ACCOUNT UNLOCK;

授权
grant connect to ysbrc;
grant create session to ysbrc;
grant resource to ysbrc;
grant create table to ysbrc;

相关文章

发表新评论