SYS and Extended Data Types

It is a widely known fact that Oracle supports a maximum size of 32,767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types (Extended Data Types) provided that MAX_STRING_SIZE = EXTENDED. What I recently discovered is that SYS can create such columns even when MAX_STRING_SIZE = STANDARD. It is possible in 12.2, 18c, 19c. I am unsure about 12.1 since I do not have any of those.

Firstly, let us try to create a table with a VARCHAR2(32767) column as non-SYS user:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL> conn tc/tc@localhost/pdb2 Connected. SQL> sho parameter max_string_size   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ max_string_size                      string      STANDARD SQL> select banner_full from v$version;   BANNER_FULL -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0     SQL> create table t(c varchar2(32767)); create table t(c varchar2(32767))                           * ERROR at line 1: ORA-00910: specified length too long for its datatype

It fails as expected.

Now I try the same as SYS - I also switch to my root container to verify that MAX_STRING_SIZE = STANDARD everywhere:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

SQL> conn sys/Oracle123@localhost/orcl2 as sysdba Connected. SQL> alter session set container=cdb$root;   Session altered.   SQL> sho parameter max_string_size   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ max_string_size                      string      STANDARD SQL> alter session set container=pdb2;   Session altered.   SQL> sho parameter max_string_size   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ max_string_size                      string      STANDARD SQL> select banner_full from v$version;   BANNER_FULL -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0     SQL> create table t(c varchar2(32767));   Table created.   SQL> desc t  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  C                                                  VARCHAR2(32767)

Let us try to run a DML command:

?

1 2 3 4 5 6 7 8 9

SQL> insert into t values (lpad('x',32767,'x'));   1 row created.   SQL> select length(c) from t;    LENGTH(C) ----------       4000

Although it was possible to create a 32K column, somewhere between LPAD and the data layer I still got 4,000 bytes only.
But is it limited to 4,000 bytes? Not really as the example below demonstrates:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

SQL> update t   2     set c=c||'y';   1 row updated.   SQL> select length(c) from t;    LENGTH(C) ----------       4001   SQL> SQL> update t   2     set c=c||c;   1 row updated.   SQL> select length(c) from t;    LENGTH(C) ----------       8002   SQL> SQL> update t   2     set c=c||c;   1 row updated.   SQL> select length(c) from t;    LENGTH(C) ----------      16004   SQL> SQL> update t   2     set c=c||c;   1 row updated.   SQL> select length(c) from t;    LENGTH(C) ----------      32008   SQL> SQL> update t   2     set c=c||c; update t * ERROR at line 1: ORA-01489: result of string concatenation is too long     SQL> select length(c) from t;    LENGTH(C) ----------      32008   SQL> SQL> update t   2     set c=c||lpad('x',759,'x');   1 row updated.   SQL> select length(c) from t;    LENGTH(C) ----------      32767   SQL> SQL> update t   2     set c=c||'x'; update t        * ERROR at line 1: ORA-01489: result of string concatenation is too long     SQL> select length(c) from t;    LENGTH(C) ----------      32767

As expected Oracle stores the column as LOB under the hood:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;   DBMS_METADATA.GET_DDL('TABLE','T') --------------------------------------------------------------------------------     CREATE TABLE "SYS"."T"    (    "C" VARCHAR2(32767)    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "SYSTEM"     SQL> SQL> select column_name, segment_name, index_name, securefile   2    from user_lobs   3   where table_name='T';   COLUMN_NAME SEGMENT_NAME              INDEX_NAME                SECUREFILE ----------- ------------------------- ------------------------- ---------- C           SYS_LOB0000023954C00001$$ SYS_IL0000023954C00001$$  NO

Turns out that Oracle uses that internally:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> drop table t;   Table dropped.   SQL> SQL> select table_name, column_name, data_length   2    from user_tab_cols   3   where data_type = 'VARCHAR2'   4     and data_length > 4000;   TABLE_NAME                COLUMN_NAME          DATA_LENGTH ------------------------- -------------------- ----------- OPATCH_SQL_PATCHES        NODE_NAMES                 32000 SYSDBIMFS_METADATA$       VALUE                       4096 SCHEDULER$_CREDENTIAL     KEY                        32767 V_$DIAG_LOG_EXT           SUPPLEMENTAL_DETAILS        4003

Originally I discovered that Oracle uses VARCHAR2(32767) in its scripts which threw me into a tizzy since the scripts were running without errors in MAX_STRING_SIZE = STANDARD databases.
It is yet another confirmation that it is not a good idea to use SYS to do things. SYS is a "special" user.