Home » SQL & PL/SQL » SQL & PL/SQL » Username length limitation in Oracle
Username length limitation in Oracle [message #281426] Fri, 16 November 2007 16:45 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
Is any username length limitation exist in Oracle?
Re: Username length limitation in Oracle [message #281427 is a reply to message #281426] Fri, 16 November 2007 16:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
30 characters
Re: Username length limitation in Oracle [message #680449 is a reply to message #281427] Thu, 14 May 2020 18:06 Go to previous messageGo to next message
pepperB
Messages: 1
Registered: May 2020
Location: Seattle, WA USA
Junior Member
As of Oracle Database 12cR2 (12.2.0.1+), the maximum length of most identifiers is now 128 characters -- including USERNAME.

Re: Username length limitation in Oracle [message #680450 is a reply to message #680449] Fri, 15 May 2020 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is bytes not characters.
Will you update all topics mentioning the length of an identifier? Smile
There are sure many 10+ years topics that have obsolete answers, thanks to also update them. Wink



[Updated on: Fri, 15 May 2020 01:05]

Report message to a moderator

Re: Username length limitation in Oracle [message #680647 is a reply to message #680449] Fri, 29 May 2020 06:41 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
pepperB wrote on Thu, 14 May 2020 19:06
As of Oracle Database 12cR2 (12.2.0.1+), the maximum length of most identifiers is now 128 characters -- including USERNAME.

but ONLY if you

ALTER SYSTEM SET max_string_size=extended;

Please note that once you do that, you can never go back to the old sizes

.. yes, why reopen such an old thread.
Re: Username length limitation in Oracle [message #680657 is a reply to message #680647] Sat, 30 May 2020 06:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Bill B wrote on Fri, 29 May 2020 07:41
pepperB wrote on Thu, 14 May 2020 19:06
As of Oracle Database 12cR2 (12.2.0.1+), the maximum length of most identifiers is now 128 characters -- including USERNAME.

but ONLY if you

ALTER SYSTEM SET max_string_size=extended;

Please note that once you do that, you can never go back to the old sizes

.. yes, why reopen such an old thread.

You are confusing data and metadata. Parameter max_string_size=extended controls max VARCHAR2/NVARCHAR2/RAW data size limit and has nothing to do with identifiers which are metadata. As pepperB correctly noted (see 2.8.1 Database Object Naming Rules):

The maximum length of identifier names depends on the value of the COMPATIBLE initialization parameter.


Quote:

If COMPATIBLE is set to a value of 12.2 or higher, then names must be from 1 to 128 bytes long with these exceptions:

Names of databases are limited to 8 bytes.

Names of disk groups, pluggable databases (PDBs), rollback segments, tablespaces, and tablespace sets are limited to 30 bytes.
SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL> create table t12345678901234567890123456789012345678901234567890(id number);

Table created.

SQL>
SY.
Re: Username length limitation in Oracle [message #680658 is a reply to message #680657] Sat, 30 May 2020 06:47 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Thanks Sy
I always assumed that the max object name size was tied into the extended setting. I just never checked. My bad
Previous Topic: Dynamic Query into Cursor
Next Topic: ORA-06530: Reference to uninitialized composite
Goto Forum:
  


Current Time: Thu Mar 28 11:15:37 CDT 2024