LONG to CLOB conversion

 

LONG data type got deprecated a while and it is good to switch into LOB’s rather than staying with LONG, one option for switching from LONG to LOB would be to use MODIFY option of the ALTER TABLE statement.

 

Doing so would lead to increased storage for the segments, which should be taken care – while doing up this in a live production system.

 

sys@PDB2> create tablespace TS_DEMO

  2  datafile 'C:\APP\ADMINISTRATOR\ORADATA\CDB1\PDB2\TS_DEMO.dbf'

  3  size 1M autoextend on next 256K

  4  extent management local

  5  uniform size 128K ;

 

Tablespace created.

 

sys@PDB2> alter user demo default tablespace ts_demo;

 

User altered.

 

sys@PDB2> alter user demo quota unlimited on ts_demo;

 

User altered.

 

sys@PDB2> conn demo/demo@pdb2

Connected.

demo@PDB2> create table t(x long) tablespace ts_demo;

 

Table created.

 

demo@PDB2> declare

  2     txt varchar2(32000);

  3  begin

  4     for i in 1..30

  5     loop

  6             for k in (select * from dba_views

  7                     where text_length <= 32000

  8                             and text is not null)

  9             loop

 10                     txt := k.text ;

 11                     insert into t(x) values(txt);

 12             end loop;

 13             commit;

 14     end loop;

 15  end ;

 16  /

 

PL/SQL procedure successfully completed.

 

demo@PDB2> exec dbms_stats.gather_table_stats(user,'T');

 

PL/SQL procedure successfully completed.

 

demo@PDB2> column segment_name format a30

demo@PDB2> column segment_type format a30

demo@PDB2> select segment_name,segment_type,bytes

  2  from user_segments ;

 

SEGMENT_NAME                   SEGMENT_TYPE                        BYTES

------------------------------ ------------------------------ ----------

T                              TABLE                             4587520

 

demo@PDB2> select bytes

  2  from v$datafile

  3  where name like '%DEMO%';

 

     BYTES

----------

   4718592

 

 

So the table sized around 4.5 MB in storage with LONG datatype in place.

 

When converting this into CLOB datatype, things gets increased like this.

 

 

demo@PDB2> alter table t modify X CLOB;

 

Table altered.

 

demo@PDB2> select segment_name,segment_type,bytes

  2  from user_segments ;

 

SEGMENT_NAME                   SEGMENT_TYPE                        BYTES

------------------------------ ------------------------------ ----------

SYS_IL0000075848C00001$$       LOBINDEX                           131072

SYS_LOB0000075848C00001$$      LOBSEGMENT                        7340032

T                              TABLE                             2359296

 

demo@PDB2> select bytes

  2  from v$datafile

  3  where name like '%DEMO%';

 

     BYTES

----------

  14680064

 

demo@PDB2>

 

 

The table has grown up to 14.5 MB during this LONG to CLOB conversion, so be aware of this space implication before attempting a conversion.