Migrating Existing Tables from LONG to LOBs

Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types

ALTER TABLE now allows a LONG column to be modified to CLOB or NCLOB and a LONG_RAW column to be modified to BLOB. The syntax is as follows:

ALTER TABLE [<schema>.]<table_name>
   MODIFY ( <long_column_name> { CLOB | BLOB | NCLOB } 
  [DEFAULT <default_value>]) [LOB_storage_clause];

For example, if you had a table with the following definition:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);

you can change the column long_col in table Long_tab to datatype CLOB as follows:

ALTER TABLE Long_tab MODIFY ( long_col CLOB );

Note: The new ALTER TABLE statement only modifies either of the following: A LONG column to a CLOB or an NCLOB column A LONG RAW column to a BLOB column It will not modify a VARCHAR or a RAW column.

Note: In the new ALTER TABLE statement to change a LONG column to a LOB, the only other operations allowed are: Specifying the default value for the LOB column Specifying the LOB storage clause for the column being changed from LONG to LOB Any other ALTER TABLE operation is not allowed with this operation.

Note: Migrating LONGs to LOBs: Method Used in Oracle8i This method of migrating LONGs to LOBs replaces the following method used in Oracle8i. Oracle8i added a new operator on LONGs called TO_LOB(). TO_LOB() copies the LONG to a LOB. You can use CREATE TABLE AS SELECT or INSERT AS SELECT statements with the TO_LOB operator to copy data from the LONG to the LOB. For example, if you have a table with the following definition: CREATE TABLE Long_tab (id NUMBER, long_col LONG); Do the following: CREATE TABLE Lob_tab (id NUMBER, clob_col CLOB); INSERT INTO Lob_tab SELECT id, TO_LOB(long_col) FROM long_tab; DROP TABLE Long_tab; CREATE VIEW Long_tab (id, long_col) AS SELECT * from Lob_tab; This series of operations is equivalent to changing the datatype of the column Long_col of table Long_tab from LONG to CLOB. With this method (the method of choice prior to this release) you have to create all the constraints, triggers, and indexes on the new table again.

All Constraints of LONG Column are Maintained

All constraints of your previous LONG columns are maintained for the new LOB columns. The only constraint allowed on LONG columns are NULL and NOT-NULL. To alter the constraints for these columns, or alter any other columns or properties of this table, you have to do so in a subsequent ALTER TABLE statement.

Default Values for LONG are Copied to LOB

If you do not specify a default value, the default value for the LONG column is copied to the new LOB column.

Most Triggers Remain Valid

Most of the existing triggers on your table are still usable, however two types of triggers can cause issues.

See: "LONG-to-LOB Migration Limitations" for more details.

Indexes Must be Rebuilt -- Use ALTER INDEX...REBUILD

Domain indexes on the LONG column must be dropped before ALTERing the LONG column to LOB.

All other indexes, including domain and functional indexes on all columns of the table, will be unusable and must be rebuilt using the ALTER INDEX <index name> REBUILD statement.

Rebuilding Indexes After a LONG to LOB Migration

To rebuild your indexes on a given table, after a LONG to LOB migration, use the following steps:

1. Drop the domain indexes on the LONG column, if any

2. ALTER TABLE Long_tab MODIFY ( long_col CLOB...)...;

3. SELECT index_name FROM user_indexes WHERE table_name='LONG_TAB';

   Note: The table name has to be capitalized in this query.

4. For all indexes <index> listed in step 3, issue the command:

   ALTER INDEX <index> REBUILD
   
   

5. Create the domain index on the LOB column, if desired.

Space Requirements are Temporarily Doubled

The ALTER TABLE MODIFY LONG->LOB statement copies the contents of the table into a new space, and frees the old space at the end of the operation. This temporarily doubles the space requirements. But the advantage is that after the transformation, the table will not have any embedded NULLs, so the performance of subsequent DMLs or queries is good.

LOGGING

During migration, the redo changes for the table are logged only if the table has LOGGING on. Redo changes for the column being converted from LONG to LOB are logged only if the storage characteristics of the LOB indicate LOGGING. The default value for LOGGING|NOLOGGING for the LOB is inherited from the tablespace in which the LOB is being created.

To prevent generation of redo space during migration, do the following to migrate smoothly:

1. ALTER TABLE Long_tab NOLOGGING;

2. ALTER TABLE Long_tab MODIFY ( long_col CLOB [default <default_val>]) LOB (long_col) STORE AS (... NOLOGGING...);

3. ALTER TABLE Long_tab MODIFY LOB long_col STORE AS (...LOGGING...);

4. ALTER TABLE Long_tab LOGGING;

5. Take a backup of the tablespaces containing the table and the LOB.