Posted by In 12c, there was much rejoicing when VARCHAR2 columns could be extended from beyond their current 4000 byte limit to 32767. But whatever the limit is, an Oracle block is an Oracle block and there are limits to what you can fit inside it. Hence an extended VARCHAR2 column is stored as a CLOB under the covers. You can see this easily with the demo below – the moment we add a row, a LOB comes into existence.
SQL> create table t ( x int, y varchar2(32767));
Table created.
SQL>
SQL> insert into t values (1,rpad('x',32000,'x'));
1 row created.
SQL> select * from user_lobs
2 where table_name = 'T'
3 @pr
==============================
TABLE_NAME : T
COLUMN_NAME : Y
SEGMENT_NAME : SYS_LOB0000132155C00002$
TABLESPACE_NAME : USERS
INDEX_NAME : SYS_IL0000132155C00002$
CHUNK : 8192
PCTVERSION :
RETENTION :
FREEPOOLS :
CACHE : YES
LOGGING : YES
ENCRYPT : NO
COMPRESSION : NO
DEDUPLICATION : NO
IN_ROW : YES
FORMAT : ENDIAN NEUTRAL
PARTITIONED : NO
SECUREFILE : YES
SEGMENT_CREATED : YES
RETENTION_TYPE : DEFAULT
RETENTION_VALUE :
PL/SQL procedure successfully completed.
The data you insert does not matter; it is the ceiling of the VARCHAR2 size that dictates that we are going to use a CLOB. For example, if I drop that table and put just a single character into it, the LOB segment is still created
SQL> drop table t purge;
Table dropped.
SQL> create table t ( x varchar2(20000));
Table created.
SQL> insert into t values ('x');
1 row created.
SQL> select * from user_lobs
2 where table_name = 'T'
3 @pr
==============================
TABLE_NAME : T
COLUMN_NAME : X
SEGMENT_NAME : SYS_LOB0000132171C00001$
TABLESPACE_NAME : USERS
INDEX_NAME : SYS_IL0000132171C00001$
CHUNK : 8192
PCTVERSION :
RETENTION :
FREEPOOLS :
CACHE : YES
LOGGING : YES
ENCRYPT : NO
COMPRESSION : NO
DEDUPLICATION : NO
IN_ROW : YES
FORMAT : ENDIAN NEUTRAL
PARTITIONED : NO
SECUREFILE : YES
SEGMENT_CREATED : YES
RETENTION_TYPE : DEFAULT
RETENTION_VALUE :
PL/SQL procedure successfully completed.
And if you are thinking that this is due to the 8k block size versus the potential maximum size of 32767 bytes, then rest assured, you will observe the same behaviour in a larger block size where a string could theoretically fit in a block.
SQL> alter system set db_16k_cache_size = 20m;
System altered.
SQL> create tablespace demo datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF' size 100m blocksize 16k;
Tablespace created.
SQL> create table t ( x varchar2(14000)) tablespace demo;
Table created.
SQL> insert into t values (rpad('x',13000,'x'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from user_lobs
2 where table_name = 'T'
3 @pr
==============================
TABLE_NAME : T
COLUMN_NAME : X
SEGMENT_NAME : SYS_LOB0000132168C00001$
TABLESPACE_NAME : DEMO
INDEX_NAME : SYS_IL0000132168C00001$
CHUNK : 16384
PCTVERSION :
RETENTION :
FREEPOOLS :
CACHE : YES
LOGGING : YES
ENCRYPT : NO
COMPRESSION : NO
DEDUPLICATION : NO
IN_ROW : YES
FORMAT : ENDIAN NEUTRAL
PARTITIONED : NO
SECUREFILE : YES
SEGMENT_CREATED : YES
RETENTION_TYPE : DEFAULT
RETENTION_VALUE :
PL/SQL procedure successfully completed.
The fact that a LOB is used is no cause for alarm, but it does mean that if you ever decide that perhaps using such a large VARCHAR2 was a mistake and you would like to reduce things, you will still be using a LOB. For example, I’ll truncate the table and reduce the maximum size of the column back down to 1000 bytes.
SQL> truncate table t;
Table truncated.
SQL> alter table t modify y varchar2(1000);
Table altered.
SQL> select * from user_lobs
2 where table_name = 'T'
3 @pr
==============================
TABLE_NAME : T
COLUMN_NAME : Y
SEGMENT_NAME : SYS_LOB0000132155C00002$
TABLESPACE_NAME : USERS
INDEX_NAME : SYS_IL0000132155C00002$
CHUNK : 8192
PCTVERSION :
RETENTION :
FREEPOOLS :
CACHE : YES
LOGGING : YES
ENCRYPT : NO
COMPRESSION : NO
DEDUPLICATION : NO
IN_ROW : YES
FORMAT : ENDIAN NEUTRAL
PARTITIONED : NO
SECUREFILE : YES
SEGMENT_CREATED : YES
RETENTION_TYPE : DEFAULT
RETENTION_VALUE :
PL/SQL procedure successfully completed.
See how this column is still implemented as a LOB. If you want to change its characteristics back to a “standard” VARCHAR2 column, then the easiest way (now the table is empty) is to drop that column, and add it back as a new column.
SQL>
SQL> alter table t drop column y;
Table altered.
SQL> alter table t add y varchar2(1000);
Table altered.
SQL>
SQL> select * from user_lobs
2 where table_name = 'T';
no rows selected
An empty table is one of the few times I’d endorse drop column, because otherwise it often just a waste of resource. I covered that in a previous video
And if you’re wondering what the thumbnail for this post represents….well, its a … blob 🙂
Learning is not a spectator sport 
….
See how this column is still implemented as a LOB. If you want to change its characteristics back to a “standard” VARCHAR2 column, then the easiest way (now the table is empty) is to drop that column, and add it back as a new column.
…..
perhaps the next easy way would be to use dbms_redefinition API – completely online – something like this:
demo@PDB1> create table t1 (x number primary key, y varchar2(6000) );
Table created.
demo@PDB1> insert into t1 values(1,'x');
1 row created.
demo@PDB1> commit;
Commit complete.
demo@PDB1> select segment_created
2 from user_lobs
3 where table_name ='T1';
SEG
---
YES
demo@PDB1> create table t2 (x number , y varchar2(80) );
Table created.
demo@PDB1> exec dbms_redefinition.can_redef_table(user,'T1');
PL/SQL procedure successfully completed.
demo@PDB1> begin
2 dbms_redefinition.start_redef_table(user,'T1','T2',
3 orderby_cols=>'x');
4 end;
5 /
PL/SQL procedure successfully completed.
demo@PDB1> variable n number
demo@PDB1> begin
2 dbms_redefinition.copy_table_dependents(user,'T1','T2',
3 num_errors=>:n);
4 end;
5 /
PL/SQL procedure successfully completed.
demo@PDB1> print n
N
----------
0
demo@PDB1> exec dbms_redefinition.finish_redef_table(user,'T1','T2');
PL/SQL procedure successfully completed.
demo@PDB1> set linesize 71
demo@PDB1> desc t1
Name Null? Type
----------------------------------- -------- -------------------------
X NOT NULL NUMBER
Y VARCHAR2(80)
demo@PDB1> select segment_created
2 from user_lobs
3 where table_name ='T1';
no rows selected
demo@PDB1>
Bob Bryla shows a trick how we can avoid this hidden LOB https://twitter.com/ExadataDBA/status/1367443903536914444
SQL> create table t(v varchar2(32000));
Table created.
SQL> select count(*) from user_lobs where table_name=’T’;
COUNT(*)
———-
1
SQL> drop table t purge;
Table dropped.
SQL> create table t(v varchar2(1));
Table created.
SQL> alter table t modify(v varchar2(32000));
Table altered.
SQL> select count(*) from user_lobs where table_name=’T’;
COUNT(*)
———-
0
The trick would work because Oracle cannot convert an existing varchar2 datatype to CLOB when u just try to use an alter command to extend varchar2(1) to varchar2(32767). But for new table with varchar2(32767) oracle directly stores as LOB.