Migrating Tables from the LONG Data Type to the LOB Data Type

You can use the ALTER TABLE statement to change the data type of a LONG column to CLOB and that of a LONG RAW column to BLOB.

The LOB data types (BFILE, BLOB, CLOB, and NCLOB) can provide many advantages over LONG data types.

In the following example, the LONG column named long_col in table long_tab is changed to data type CLOB:

SQL> ALTER TABLE Long_tab MODIFY ( long_col CLOB );

After using this method to change LONG columns to LOBs, all the existing constraints and triggers on the table are still usable. However, all the indexes, including Domain indexes and Functional indexes, on all columns of the table become unusable and must be rebuilt using an ALTER INDEX...REBUILD statement. Also, the Domain indexes on the LONG column must be dropped before changing the LONG column to a LOB.