Wednesday, March 12, 2014

Create Users in Oracle 12c - Avoiding ORA-65096


Login into Container Database - (CDB2)
=================================
First, we have to decide if the new user will be considered type COMMON_USERS or LOCAL_USERS.
For a common user, the c## prefix must be used in the user creation

SQL> connect sys/oracle@cdb2 as sysdba
Connected.

SQL> create user user1 identified by oracle default tablespace users;
create user user1 identified by oracle default tablespace users
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user c##user1 identified by oracle default tablespace users;
User created.

SQL> grant connect,resource to c##user1;
Grant succeeded.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select PDB from v$services;

PDB
------------------------------
CDB2_PDB1
CDB$ROOT
CDB$ROOT
CDB$ROOT
CDB$ROOT

Login into Pluggable Database - (CDB2_PDB1)
======================================
SQL> alter session set container=CDB2_PDB1;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB2_PDB1

SQL> alter pluggable database open;
Pluggable database altered.

SQL> create user user2 identified by oracle;
User created.

SQL> grant connect,resource to user2;
Grant succeeded.

SQL> connect user2/oracle@192.168.56.111:1521/cdb2_pdb1;
Connected.

No comments:

Post a Comment