Oracle 12c创建用户时出现ORA-65096: invalid common user or role name的错误 - 牧人池塘 - DBA杂谈录

2017-10-17

暂无评论

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

CDB和PDB是ORACLE 12C一个新特性，名词解释如下：
ultitenant Environment：多租户环境
CDB（Container Database）：数据库容器
PD（Pluggable Database）：可插拔数据库
CDB与PDB关系图
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;

Tag标签:「oracle grant 多租户」更新时间:「2021-11-03 17:33:19」阅读次数:「772」

相关博文：