Not sure if everybody already is aware of all the changes and new features that Oracle 18.104.22.168 includes but yesterday I had a short and informal conversation with one of my fellows about 22.214.171.124 and in the middle of the conversation he said something like "but remember that the prefix must be C##", after hearing that I thought immediately in the change that was introduced in 126.96.36.199, I let him know that this behavior already changed and that's why I am writing this article, in order to help anyone that wants to learn a little bit more about this change.
For this example I am using the software 188.8.131.52 of Oracle Database. With this software let's try to create the following common user while connected to CDB$ROOT:
SQL> create user DGOMEZ identified by DGOMEZ container=all;create user dgomez identified by dgomez container=all *ERROR at line 1:ORA-65096: invalid common user or role name
Why does this sentence fail? Yo should be wondering about the default behaviour, the behaviour that Oracle 12c was introduced with. When 12c was released the words "common" and "local" became very popular, and every time we talked about a common user we were talking about the prefix, or some times it was not required since the prefix was implicit as a prerequisite to create a common user. So what you can say here is that my sentence failed because it doesn't include the correct prefix 'C##' or 'c##', and you are right.
What about if I use the correct (default) prefix?
SQL> create user C##DGOMEZ identified by DGOMEZ container=all;
Yes, I was able to create the common user!.
But The first time I ran into this change (because I found the change when I haven't read about it) I remember an article that I wrote in February 2014 about the parameter "_common_user_prefix", in that article I explained that we can change the prefix of common user by modifying this hidden parameter. whenever you wanted to change the default behaviour you were able to change it and specify the prefix that you wanted.
But as I said the first time I found this change I wasn't aware about it, and I found it because I executed something like the following sentence:
So, when I looked into the output, the parameter "common_user_prefix" impressed me, because now it is not hidden. I went to my article that I wrote about the same parameter and I confirmed that at that time using 184.108.40.206 the parameter was indeed hidden. So that's how I found the change...
In 220.127.116.11 the parameter looks like this one:
SQL> show parameters COMMON_USER_PREFIX;
NAME TYPE VALUE------------------ ------ --------common_user_prefix string C##
It is not hidden, it is not dynamic (change it without bounce the instance) and the default value is "C##" in order to keep the behaviour of 18.104.22.168.
SQL> r 1* select name, value, ISINSTANCE_MODIFIABLE, ISSYS_MODIFIABLE from v$system_parameter where name='common_user_prefix'
NAME VALUE ISINSTANCE_MODIFIABLE ISSYS_MODIFIABLE------------------ ----- --------------------- -----------------common_user_prefix C## FALSE FALSE
Here are the concepts that the Oracle Documentation says you have to know about it:
In Oracle Database 12c Release 1 (22.214.171.124):
The name of a common user must begin with C## or c## and the name of a local user must not begin with C## or c##.
Starting with Oracle Database 12c Release 1 (126.96.36.199):
The name of a common user must begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C##.
The name of a local user must not begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. Regardless of the value of COMMON_USER_PREFIX, the name of a local user can never begin with C## or c##.
If the value of COMMON_USER_PREFIX is an empty string, then there are no requirements for common or local user names with one exception: the name of a local user can never begin with C## or c##. Oracle recommends against using an empty string value because it might result in conflicts between the names of local and common users when a PDB is plugged into a different CDB, or when opening a PDB that was closed when a common user was created.
Now let's see an example of how to play with this parameter.
Let's remove the value of the prefix :
SQL> alter system set common_user_prefix='' scope=spfile;
Confirming it was removed from spfile:
SQL> select name, value from v$spparameter where name='common_user_prefix';
NAME VALUE-------------------- -------common_user_prefix
Bouncing the instance:
SQL> startup force;ORACLE instance started.
Total System Global Area 2516582400 bytesFixed Size 2927528 bytesVariable Size 671089752 bytesDatabase Buffers 1828716544 bytesRedo Buffers 13848576 bytesDatabase mounted.Database opened.SQL>
Now the sentence that failed at the beginning of this article completes successfully:
SQL> create user DGOMEZ identified by DGOMEZ container=all;
Now let's play with our customised prefix:
SQL> alter system set common_user_prefix='GUATE' scope=spfile;
Total System Global Area 2516582400 bytesFixed Size 2927528 bytesVariable Size 671089752 bytesDatabase Buffers 1828716544 bytesRedo Buffers 13848576 bytesDatabase mounted.Database opened.SQL> create user C##BOSS identified by boss container=all;create user C##BOSS identified by boss container=all *ERROR at line 1:ORA-65096: invalid common user or role name
SQL> create user BOSS identified by boss container=all;create user BOSS identified by boss container=all *ERROR at line 1:ORA-65096: invalid common user or role name
SQL> create user GUATEBOSS identified by BOSS container=all;
SQL> create user guatecaptain identified by captain container=all;
Follow me for more articles like this one!
Many, Many thanks Deiby. A little comment using the logic you did , I created my user
SQL> Create User AGARCIA identified by AGARCIA container=ALL;
now suppose you need to move Data from the current containter to another container, before you remove the prefix for common user is easy identify who are the list of common user; But if you change the common_user_prefix several times , the way you need to identify the user is
Select username,user_id,common from dba_users where common='YES';