ChatGPT解决这个技术问题 Extra ChatGPT

error: ORA-65096: invalid common user or role name in oracle

I just installed Oracle, and it was missing the Scott schema. So i am trying to generate it myself. I got the sql script of Scott schema, but when i try to run the query:

create user Scott identified by tiger; 

it displays the following error:

ORA-65096: invalid common user or role name in oracle.

Basically it is not allowing me to create a user Scott.

Why is that, and how can I fix my problem?

That is impossible to have in 11g, you ought to be on 12c.
Maybe you installed an 11g client, but you're connecting to a 12c database? What exactly did you install?
Append C## before username.
I am using 19c but still facing same issue. any suggestion?

J
Jon Heller

99.9% of the time the error ORA-65096: invalid common user or role name means you are logged into the CDB when you should be logged into a PDB. For example, if you used the default 19c installation settings, you should login to ORCLPDB (the PDB) instead of ORCL (the CDB).

DANGER - If you insist on creating users the wrong way, follow the steps below.

Setting undocumented parameters like this (as indicated by the leading underscore) should only be done under the direction of Oracle Support. Changing such parameters without such guidance may invalidate your support contract. So do this at your own risk.

Specifically, if you set "_ORACLE_SCRIPT"=true, some data dictionary changes will be made with the column ORACLE_MAINTAINED set to 'Y'. Those users and objects will be incorrectly excluded from some DBA scripts. And they may be incorrectly included in some system scripts.

If you are OK with the above risks, and don't want to create common users the correct way, run this command before creating the user:

alter session set "_ORACLE_SCRIPT"=true;  

I found the answer here


Be aware, it is a hidden parameter and to be used only when recommended by Oracle support.
It is dangerous to use underscore (hidden) parameters in Production systems, as it may invalidate your Support contract. So you should advise people to set them without giving the appropriate warning.
this is wrong and not supported in oracle, I have used it and it caused another internal issues.
@Victor - Because we're only allowed to change them when directed to do so by Oracle Support. Like I said, setting undocumented parameters off our own bat may invalidate our support contract. More generally, Oracle's default settings for documented and undocumented parameters are usually sound for all applications, and don't need changing. Tweaking underscore parameters appeals to people who enjoy the thrill of arcane access, which is the worst reason to tweak them. But if you don't have a support contract to worry about, you are free to risk damaging your system in any way you choose ;-)
why don't you show how to fix it the proper way then?
L
Lalit Kumar B

I just installed oracle11g ORA-65096: invalid common user or role name in oracle

No, you have installed Oracle 12c. That error could only be on 12c, and cannot be on 11g.

Always check your database version up to 4 decimal places:

SELECT banner FROM v$version WHERE ROWNUM = 1;

Oracle 12c multitenant container database has:

a root container(CDB)

and/or zero, one or many pluggable databases(PDB).

You must have created the database as a container database. While, you are trying to create user in the container, i.e. CDB$ROOT, however, you should create the user in the PLUGGABLE database.

You are not supposed to create application-related objects in the container, the container holds the metadata for the pluggable databases. You should use the pluggable database for you general database operations. Else, do not create it as container, and not use multi-tenancy. However, 12cR2 onward you cannot create a non-container database anyway.

And most probably, the sample schemas might have been already installed, you just need to unlock them in the pluggable database.

For example, if you created pluggable database as pdborcl:

sqlplus SYS/password@PDBORCL AS SYSDBA

SQL> ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY tiger;

sqlplus scott/tiger@pdborcl

SQL> show user;
USER is "SCOTT"

To show the PDBs and connect to a pluggable database from root container:

SQL> show con_name

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

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

SQL> alter session set container = ORCLPDB;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
ORCLPDB

I suggest read, Oracle 12c Post Installation Mandatory Steps

Note: Answers suggesting to use the _ORACLE_SCRIPT hidden parameter to set to true is dangerous in a production system and might also invalidate your support contract. Beware, without consulting Oracle support DO NOT use hidden parameters.


Above link "Oracle 12c Post Installation Mandatory Steps" is broken. New link: docs.oracle.com/database/121/LADBI/post_inst_task.htm#LADBI8084
@Heri Are you sure? Because it's working fine Oracle 12c Post Installation Mandatory Steps
The default PDB name for Oracle 18c XE is "XEPDB1".
Why doing things the simple way, when you can do it Oracle way 🤷‍♂️
I am using 19c but still facing same issue. any suggestion?
t
t0r0X

In Oracle 12c and above, we have two types of databases:

Container DataBase (CDB), and Pluggable DataBase (PDB).

If you want to create an user, you have two possibilities:

You can create a "container user" aka "common user". Common users belong to CBDs as well as to current and future PDBs. It means they can perform operations in Container DBs or Pluggable DBs according to assigned privileges. create user c##username identified by password; You can create a "pluggable user" aka "local user". Local users belong only to a single PDB. These users may be given administrative privileges, but only for that PDB inside which they exist. For that, you should connect to pluggable datable like that: alter session set container = nameofyourpluggabledatabase; and there, you can create user like usually: create user username identified by password;

Don't forget to specify the tablespace(s) to use, it can be useful during import/export of your DBs. See this for more information about it https://docs.oracle.com/database/121/SQLRF/statements_8003.htm#SQLRF01503


This answer's style is my favourite, quick to use.
G
Gryu
SQL> alter session set "_ORACLE_SCRIPT"=true;  
SQL> create user sec_admin identified by "Chutinhbk123@!";

P
Pado

If your goal is to create an Oracle user and then use it to do stuff with your DB you probably want to consider doing this:

log as sysdba

create a pdb (a.k.a. pluggable database)

create a user for your pdb

What follows assume you are working with a db in localhost, if not simply change "localhost" with your db uri.

Sample code

Log as sysdba:

$ sqlplus sys/<your_admin_pws>@localhost as sysdba

then execute this:

create pluggable database MYDATABASE
admin user Scott identified by tiger;
file_name_convert = ('/pdbseed/', '/mydatabase/')
;

alter pluggable database MYDATABASE open;

then you may want to grant some permission to user Scott: log out from @localhost and log back in as sysdba to your new db

$ sqlplus sys/<your_admin_pws>@localhost/MYDATABASE as sysdba

and grant Scott whatever permission you want, e.g.

grant connect to Scott;
grant create view to Scott;
grant create table to Scott;
grant create trigger to Scott;

now you are good to go: you have an empty database instance and a user. Just log in by doing

$ sqlplus Scott/tiger@localhost/MYDATABASE

Bonus

I had problem with tablespaces and quotas after that. Log with sqlplus sys/<your_admin_pws>@localhost/MYDATABASE as sysdba

You can list all tablespaces with

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;

You can create a new tablespace with

CREATE TABLESPACE TABLESPACENAME DATAFILE 'tablespace_datafile.dat' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

You can drop your broken tablespaces with

Drop tablespace TABLESPACENAME including contents and datafiles;

You can grant "access" to the tablespace to Scott with

alter user Scott quota unlimited on TABLESPACENAME;

N.B. quota unlimited is probably a bad practice, check oracle docs on how to limit user's quota


R
Rohim Chou

In my case, after default oracle installation, I first connected to orcl which cause the problem. And reconnected to orclpdb (Pluggable DataBase, PDB) resolve the issue.

https://i.stack.imgur.com/d0Lpa.png


+1 These details are useful for people new to Oracle. I'm going to steal some of your answer and edit it into the accepted answer. (Unfortunately, most people won't scroll down to see your new answer because this thread if full of bad information.)
For me this worked. Don't use SID, use Service name.
b
bigtheo

https://i.stack.imgur.com/unlTO.png

global users, which belong to the CBD container. to create a user in this container you must do Create user c##username identified by passwod; local users: which belong to the PDBS databases (are elements which relate to the parent CBD containers). the syntax you used matches the creation of local users.

if you are in pluggable database(PDBS) do this to solve your problem :

create user c##Scott identified by tiger; 

note : you must add c## before the username.


P
PolyGlot

Might be, more safe alternative to "_ORACLE_SCRIPT"=true is to change "_common_user_prefix" from C## to an empty string. When it's null - any name can be used for common user. Found there.

During changing that value you may face another issue - ORA-02095 - parameter cannot be modified, that can be fixed in a several ways, based on your configuration (source).

So for me worked that:

alter system set _common_user_prefix = ''; scope=spfile;

S
SAR

this part may fix your problem.

alter session set "_oracle_script"=true;

follow this:

> sqlplus /nolog
> connect sys / as sysdba
> alter session set "_oracle_script"=true;
> create user user1 identified by 1;

and the user is created..


This is almost certainly a bad idea, as described in the accepted answer.
@JonHeller so what is ur solution and still the accepted answer is null.
There are multiple other answers, including answers that are almost identical to this one.
T
Tomáš Záluský

Advice for docker users getting this error:

I got same error trying to create docker image of Oracle19c and the cause was exactly what accepted answer says. My docker image was intended as copy of original database for testing and development in isolated environment so I was aimed at the best possible similarity with original. Unfortunately the original database was configured as nonCDB single-instance database which is not default setting in standard Oracle image.

First I tried alternative image where it was supported but it has another drawbacks (twice larger, hardcoded VOLUME).

Finally I pulled out $ORACLE_BASE/*.sh scripts from both original and alternative image, analyzed differences and applied relevant differences onto original scripts. The essential change was in dbca.rsp.tmpl file and in removing usage of $ORACLE_PDB env reconfiguring alter system startup SQL scripts, also some further related changes such as in healthcheck must be performed.

Then oracle successfully started in nonCDB mode. It can be verified trying create user foo identified by foo; as system user (which succeeds) or the select name, cdb, con_id from v$database; (which returns cdb=0).


l
lofihelsinki

Create user dependency upon the database connect tools

sql plus
SQL> connect as sysdba;
Enter user-name: sysdba
Enter password:
Connected.
SQL> ALTER USER hr account unlock identified by hr;
User altered
 then create user on sql plus and sql developer

Could you please format your code properly and give some context why this solves the problem better than the other answers?

关注公众号,不定期副业成功案例分享
Follow WeChat

Success story sharing

Want to stay one step ahead of the latest teleworks?

Subscribe Now