Converting LONG to CLOB

Some folks still are stuck with LONG columns, and are keen to move to LOB.  Since version 9, we’ve had a nice facility to do that – just with a simple alter command.

You can now simply issue “alter table (longcol CLOB)” to perform the conversion. This is a neat tool, but be aware of the space implications before attempting a conversion.

Here is a simple example to demonstrate:

-- First a very small tablespace so we can monitor temporary space requirements

SQL> create tablespace DEMO datafile 'C:\ORACLE\ORADATA\NP12\DEMO01.DBF' size 1m
  2  autoextend on next 256k
  3  extent management local uniform size 128k;

Tablespace created.

SQL> alter user mcdonac default tablespace DEMO;

User altered.

-- Now a basic table with a LONG, we'll load some data from DBA_VIEWS

SQL> create table testlong ( text long );

Table created.

SQL> declare
  2      p varchar2(32767);
  3  begin
  4   for j in 1 .. 10 loop
  5     for i in ( select text from dba_views where text_length < 32500 ) loop
  6        p := i.text;
  7       insert into testlong values (p);
  8     end loop;
  9     commit;
 10   end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','TESTLONG')

PL/SQL procedure successfully completed.

SQL> col bytes format 999,999,999,999
SQL> col name format a60
SQL> set lines 120
SQL> select bytes, name
  2  from v$datafile
  3  where name like '%DEMO01%';

           BYTES NAME
---------------- ------------------------------------------------------------
      80,216,064 C:\ORACLE\ORADATA\NP12\DEMO01.DBF

SQL> select bytes from user_segments
  2  where tablespace_name = 'DEMO';


  2  from user_tables
  3  where table_name = 'TESTLONG';

---------- ---------- ------------ ----------- ----------
     68130       9633            0           0          0

So as you can see, the table in its current form is using about 80M of space and the tablespace has grown to match. Now we convert that column to a clob

SQL> alter table TESTLONG modify ( text clob);

Table altered.

SQL> col segment_name format a30

SQL> select segment_name, bytes
  2  from user_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                              BYTES
------------------------------ ----------------
TESTLONG                             49,938,432
SYS_IL0000105876C00001$$                131,072
SYS_LOB0000105876C00001$$            42,074,112

So we have some additional segments (as we would expect) and the amount of space used has grown by about 10%. This seems pretty perfectable good. But take a look at the tablespace

SQL> select bytes, name
  2  from v$datafile
  3  where name like '%DEMO%';

           BYTES NAME
---------------- ------------------------------------------------------------
     172,490,752 C:\ORACLE\ORADATA\NP12\DEMO01.DBF


If we look at the tablespace, it has grown to 170M so at least temporarily, over double the space was required. I’m not saying that this is a bad thing – but its certainly something to be aware of when you doing CLOB conversions

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s