Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Migrating From LONGs to LOBs, 4 of 14
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 );
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.
If you do not specify a default value, the default value for the LONG column is copied to the new LOB column.
Most of the existing triggers on your table are still usable, however two types of triggers can cause issues.
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.
To rebuild your indexes on a given table, after a LONG to LOB migration, use the following steps:
ALTER INDEX <index> REBUILD
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.
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:
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|