Introduction:
In Oracle Database 12c and later versions, users may encounter the ORA-65096 error when attempting to create a new user with a name that conflicts with a common user or role name. This error arises due to restrictions on naming conventions for common users and roles in Oracle Database. Fortunately, there’s a workaround that allows users to create such users without encountering the error.
Understanding the Issue:
In Oracle Database 12c and later versions, common users and roles share the same namespace, which can lead to conflicts when attempting to create a user with a name that matches a common user or role name. This conflict results in the ORA-65096 error, indicating an invalid common user or role name.
Solution: Setting _ORACLE_SCRIPT Session Parameter:
To address this issue, users can set the _ORACLE_SCRIPT session parameter to true. This setting tells Oracle Database to treat the CREATE USER and ALTER USER statements as though they were being run in SQL*Plus with the SET ROLE NONE command, allowing for the creation of users with names that would normally trigger the ORA-65096 error.
Implementation:
Users can execute the following SQL statement to set the _ORACLE_SCRIPT session parameter:
ALTER SESSION SET "_ORACLE_SCRIPT"=true;Once this parameter is set, users can proceed to create users with names that would otherwise cause conflicts without encountering the ORA-65096 error.
Conclusion:
Encountering the ORA-65096 error when attempting to create users with conflicting names can be frustrating. However, by setting the _ORACLE_SCRIPT session parameter to true, users can bypass this error and successfully create the desired users in Oracle Database 12c and later versions.