Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below:

create table test_table1
 (col1 nvarchar2(100));

table TEST_TABLE1 created.

desc test_table1
Name Null Type           
---- ---- -------------- 
COL1      NVARCHAR2(150)

We observed that the displayed precision of the nvarchar2 column is 1.5 times the specified precision. So if I have to replicate the precision of a column of type NVARCHAR2(375), then we need to declare it as NVARCHAR2(250).

I tried searching for an explanation but couldn't find any. Does anyone have any idea on this please? We are using Oracle-11g, connecting with Oracle SQL Developer.

NLS_SESSION_PARAMETERS

```
select * from NLS_SESSION_PARAMETERS;

+-------------------------+------------------------------+
| PARAMETER | VALUE |
+-------------------------+------------------------------+
| NLS_LANGUAGE | AMERICAN |
| NLS_TERRITORY | AMERICA |
| NLS_CURRENCY | $ |
| NLS_ISO_CURRENCY | AMERICA |
| NLS_NUMERIC_CHARACTERS | ., |
| NLS_CALENDAR | GREGORIAN |
| NLS_DATE_FORMAT | YYYY.MM.DD HH24:MI:SS |
| NLS_DATE_LANGUAGE | AMERICAN |
| NLS_SORT | BINARY |
| NLS_TIME_FORMAT | HH.MI.SSXFF AM |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIME_TZ_FORMAT | HH.MI.SSXFF AM TZR |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_DUAL_CURRENCY | $ |
| NLS_COMP | BINARY |
| NLS_LENGTH_SEMANTICS | BYTE |
| NLS_NCHAR_CONV_EXCP | FALSE |
+-------------------------+---------------------

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table.

UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you want to store 1 byte character in UTF8 it takes 1.5 bytes (1*1.5=1.5).

For further details, see:

AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]

StackExchange Database Administrators Q#125862, answer score: 4