TO_LOB

Oracle have been telling us to convert to lob's since 8.0, but finally in 9i there is a very nice tool to convert LONG/LONG RAW to the new LOB datatypes. You can now simply issue "alter table (longcol CLOB)" to perform the conversion. On the surface, this seems a neat trick, but be aware of the space implications before attempting a conversion.

Here is a simple example to demonstrate the issue:

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

SQL> create tablespace tiny
  2  datafile 'G:\ORA9I\ORADATA\DB9\TINY.DBF' size 2M
  3  extent management local uniform size 64k;

Tablespace created.

-- 
-- Then we'll let it grow in 256k increments if required
--

SQL> alter database datafile 'G:\ORA9I\ORADATA\DB9\TINY.DBF' autoextend on next 256k;

Database altered.

SQL> alter user mcdonac default tablespace tiny;

User altered.

-- 
-- Now a basic table with a LONG, thats just a straight copy (4x over) from DBA_VIEWS
-- (Interestingly the COPY command on this machine NT 9.0.1.2 bombed out which is why 
--  I'm using PL/SQL)
--

SQL> create table testlong ( text long );

Table created.

SQL> declare
  2      p varchar2(32767);
  3  begin
  4   for j in 1 .. 4 loop
  5     for i in ( select text from dba_views ) 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> analyze table testlong estimate statistics;

Table analyzed.

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 '%TIN%';

           BYTES NAME
---------------- ------------------------------------------------------------
       6,815,744 G:\ORA9I\ORADATA\DB9\TINY.DBF

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

           BYTES
----------------
       6,553,600

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN,CHAIN_CNT
  2  from user_tables
  3  where table_name = 'TESTLONG';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  CHAIN_CNT
---------- ---------- ------------ ----------- ----------
      9212        796            3         702         64

-- 
-- So as you can see, the table in its current form is using about 6.5M 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.

-- 
-- Notice now that we have some additional segment (as we would expect) and
-- the amount of space used has popped out to about 8M.  This seems pretty good
--

SQL> col segment_name format a30
SQL> select segment_name, bytes
  2  from user_segments
  3  where tablespace_name = 'TINY';

SEGMENT_NAME                              BYTES
------------------------------ ----------------
TESTLONG                              5,701,632
SYS_IL0000031894C00001$$                 65,536
SYS_LOB0000031894C00001$$             2,228,224

-- 
-- But here is the "gotcha".  If we look at the tablespace, it has grown to 14M
-- so at least temporarily, over double the space was required. 
--

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

           BYTES NAME
---------------- ------------------------------------------------------------
      14,680,064 G:\ORA9I\ORADATA\DB9\TINY.DBF

Obviously this is a pretty basic example, but I struggled to come up with any combination, for example, disabling/enabling storage inline, having many additional non-LONG columns in the source table, "long" LONGs, "short" LONGs etc that did not require close to double the space.

I'm not saying that this is a bad thing - but its certainly something to remember before leaping onto the CLOB conversion bandwagon.