4

Using Oracle 11gR2

You can't create a username starting with a number:

SQL> create user 123 identified by temp;
create user 123 identified by temp
            *
ERROR at line 1:
ORA-01935: missing user or role name

However, you can create it as:

SQL> create user "123" identified by temp;

User created.

Somebody knows possible problems with this kind of users?

Somebody knows oracle rules/reasons why you can't create it without quotes, ie, to have usernames starting with numbers?

Thanks in advance

Osy
  • 1,613
  • 5
  • 21
  • 35
  • 2
    I think your data is read as a `NUMBER` while if you have a quotations, the user is read as `VARCHAR`. It is not the about the issue about username starting with a number. – Mark Jan 10 '14 at 02:01
  • Thanks @ChristianMark, right technically is the reason to be allowed to create "123" user, but my question is about possible problems or better, to know why is not a good practice to have this kind of usernames. For some reason Oracle does no allow to create usernames starting with numbers (not varchar). – Osy Jan 10 '14 at 18:14
  • 1
    I have check the table ALL_USERS and found that the data type for USERNAME is VARCHAR2(128). I believe you may create any username as long as it is with this data type. – Nick Mar 04 '19 at 06:47

4 Answers4

4

Problems with quoted identifiers

Quoted identifiers can be successfully used for almost any Oracle object, including users. In theory, they work everywhere. In practice, you will run into many inconveniences and problems with quoted identifiers.

From the SQL Language Reference: "Note: Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects."

Once you use double quotes, every reference to that object must use double quotes, and the correct case. You'll find lots of problems with tools that don't always use double quotes. And problems with scripts that look at metadata and don't always add double quotes. Quoted identifiers are just asking for trouble.

Why does Oracle have quoted identifiers?

This question is harder to answer, but I would guess limiting the types of characters used by objects makes parsing much easier. SQL already has a lot of keywords, and has many weird language ambiguities. If object names started with numbers it would make it difficult to differentiate between real numbers and objects.

For example, without quoted identifiers, this simple statement could be a mess:

select 1.1 + 2.2 from some_table;

Without restricting object names, 1.1 could be a huge number of things, and the parser would have to look for objects named "1", and then dependent objects named "1", and then determine if that takes precedence over the number "1.1".

Weird names are possible in languages, but I assume when someone wrote the first SQL compiler 40 years ago they decided not to make their lives so complicated just to accommodate a few weird names.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
0

Check if the user name is not present in reserved words and doesn't start with number:

SELECT  *
FROM v$reserved_words
ORDER BY keyword
Leonid Dashko
  • 3,657
  • 1
  • 18
  • 26
0

If you are creating user try this:

alter session set "_ORACLE_SCRIPT"=true;

CREATE USER oe IDENTIFIED BY oe;
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Gaurav Singh
  • 11
  • Setting this underscore parameter is potentially dangerous, and should almost always be avoided, as described in [this answer](https://stackoverflow.com/a/41490385/409172). – Jon Heller Jan 08 '22 at 23:39
0

check your connection type is cdb or not. if it is cdb as shown in the below image

use prefix c## before the username in the command for creating user

rahul
  • 1