Posted by 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';
BYTES
----------------
80,084,992
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
---------- ---------- ------------ ----------- ----------
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
SQL>
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
Learning is not a spectator sport 