I am trying to create a new user from Oracle SQL Developer. I made a connection with the user Sys and the password that I entered in the installation of Oracle Database XE 18c.
When creating the user I get the error ORA-65096, I have searched the internet and the solution I found is to write the following:
alter session set "_ORACLE_SCRIPT" = true;
However, I want to know if it is possible to create the new user without using any SQL statement or script and do it from the Oracle SQL Developer interface, do I have to login with a different user than Sys to create a new user? or what do I have to do? Could someone give me a detailed explanation please. I am learning how to use this database and I want to try to understand what I am doing.
oerr ora 65096
65096, 00000, "invalid common user or role name" // *Cause: An attempt was made to create a common user or role with a name // that was not valid for common users or roles. In addition to >the // usual rules for user and role names, common user and role names // must consist only of ASCII characters, and must contain the >prefix // specified in common_user_prefix parameter. // *Action: Specify a valid common user or role name.
This implies that you are connected to the root container instead of to a regular plug in database (pdb) where applications should be built. The solution for you is not to overrule setting to enable building an application in the root container but to connect to the pdb that has been made to host your application.
you can check the available pdb's by viewing v$pdbs. The special pdb cdb$root is as the name already tries to tell, the root container. show PDBS will give similar output.
Using alter session set container = [pdb_name]; can be used to switch to your container of choice where regular rules apply.
Even better is to directly connect to that pdb using sqlnet.