If you’re getting an ORA-65049 error, you’re probably trying to create a local database user (a local database user is a user without a c## prefix) in the CDB$ROOT container for a multi-tenant database. A few solutions exist:
- If you’re trying to create a user that can log into any pluggable database, you can add a c## prefix to the username.
E.g. Below creates a global user named c##kaley with the password ThisIsMyPassword and grants them DBA rights. This user can log into any of the pluggable databases.
grant dba to c##kaley identified by ThisIsMyPassword container=all;
- If you’re trying to create a user that can log in to just a specific pluggable database, you can create the user within a specific container.
E.g. Below changes the container to the PORA19c1 container and creates the local user KALEY within that container, and grants the user DBA privilege. The created user can ONLY login to the PORA19c1 database
alter session set container = pora19c1;
grant dba to kaley identified by ThisIsMyPassword container = current;
- **UNDOCUMENTED** If you really, really want to create a user that can log into the root database, but doesn’t start with C## then that’s possible (but, I’m not sure if something like this would have Oracle Support’s blessing, so proceed with caution at your own risk). Note that this is not a global user–the user isn’t created in the underlying databases.
If you run the below statements as the SYS user, it creates a user that can only log in to the container database named KALEY, and grants the user DBA privilege. Note that in this situation, KALEY is considered an “Oracle Managed” user…same as SYS or SYSTEM or DBSNMP, etc.
alter session set "_oracle_script" = true;
grant dba to kaley identified by ThisIsMyPassword;
Note that if you need to drop this account later, you’ll again have to make sure that “_oracle_script” is set to true when you try to drop the user. Otherwise, you’ll get an “ORA-28014: cannot drop administrative users” error.
- **UNDOCUMENTED** If you really, really, really want to create a user that can log into ANY database (not just the root database, but an actual global user) but without the “C##” prefix, you can change an underscore parameter
alter system set "_common_user_prefix" = '' scope=spfile;
If you do that, and reboot your database, you’ll be allowed to create a global user without the C## prefix.
grant dba to kaley identified by ThisIsMyPassword container = all;
If you want to see what containers your database has, login as SYS, change to the CDB$ROOT container, and query v$pdbs.
alter session set container = cdb$root; select name from v$pdbs;
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets