You are going to be a little stuck here because varchar2(32k) sits "between" the typical compression mechanisms.
- For 'normal' columns, we look for repeated values to compress
- For 'clobs' we recommend securefile compression
You can see this with a simple demo
SQL>
SQL> create table t ( x varchar2(32767));
Table created.
SQL>
SQL> variable x varchar2(32000)
SQL> exec :x := '{"json":"data"}';
PL/SQL procedure successfully completed.
SQL> exec begin for i in 1 .. 6 loop :x := :x || :x || :x; end loop; end;
PL/SQL procedure successfully completed.
SQL> exec :x := :x || :x;
PL/SQL procedure successfully completed.
SQL> select length(:x) from dual;
LENGTH(:X)
----------
21870
SQL> insert into t select :x
2 from dual
3 connect by level <= 10000;
10000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks from user_tables
2 where table_name = 'T';
BLOCKS
----------
88
SQL>
SQL> select bytes from user_segments
2 where segment_name = (
3 select segment_name from user_lobs
4 where table_name = 'T' );
BYTES
----------
285409280
SQL>
SQL> alter table t move row store compress advanced;
Table altered.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks from user_tables
2 where table_name = 'T';
BLOCKS
----------
68
SQL>
SQL> select bytes from user_segments
2 where segment_name = (
3 select segment_name from user_lobs
4 where table_name = 'T' );
BYTES
----------
251789312
So we don't get a lot of benefit from compression. Internally the varchar2(32767) is likely stored as a securefile, but because its not a "genuine" one, you don't get access to clob compression
SQL> alter table t move lob ( x ) store as securefile ( compress high );
alter table t move lob ( x ) store as securefile ( compress high )
*
ERROR at line 1:
ORA-14692: STORE AS clause is not allowed for extended character type column
SQL>
My recommendation would be to look at using a clob/blob and taking advantage of securefile compression.
To see columns with low distinct values, you can see this from USER_TAB_COLUMNS, and also there is the compression advisor to gauge the benefits you might get.
https://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-advanced-compression