Labels

Thursday, March 6, 2014

Can Common user and local user name be same in oracle 12c?

In a multitenant environment, a common user is a database user whose identity and password are known in the root and in every existing and future pluggable database (PDB).
Hence common user and local user name cannot be same.
Lets proof this satement
SQL> alter system set  "_common_user_prefix"='' scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

In pfile
ora12c._common_user_prefix=''
SQL> startup pfile='C:\app\XXXXXX\product\12.1.0\dbhome_1\database\INITora12c.ORA';
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2410864 bytes
Variable Size             666896016 bytes
Database Buffers          394264576 bytes
Redo Buffers                5365760 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show con_name

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

SQL> create user demo identified by demo;

User created.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORA12C                      MOUNTED
SUBUPDB                        MOUNTED
TEST                           READ WRITE

SQL> alter session set container=TEST;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
TEST
SQL> create user demo identified by demo;
create user demo identified by demo
            *
ERROR at line 1:
ORA-01920: user name 'DEMO' conflicts with another user or role name
SQL> show con_name

CON_NAME
------------------------------
TEST
SQL> create user hello identified by hello;

User created.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user hello identified by hello;
create user hello identified by hello
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database TEST

ORA-01920: user name 'HELLO' conflicts with another user or role name

No comments:

Post a Comment