Generation of redo space can cause performance problems during the process of converting LONG columns. Redo changes for the table are logged during the conversion process 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 column indicate LOGGING. The logging setting (LOGGING or NOLOGGING) for the LOB column is inherited from the tablespace in which the LOB is created.

To prevent generation of redo space during migration, do the following before migrating your table (syntax is in BNF):

You can use the ALTER TABLE statement in SQL to convert a LONG column to a LOB column.

To do so, use the following syntax:

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 that was created as follows:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);

then you can change the column long_col in table Long_tab to data type CLOB using following ALTER TABLE statement:

ALTER TABLE Long_tab MODIFY ( long_col CLOB );

Note that when using the ALTER TABLE statement to convert a LONG column to a LOB column, only the following options are allowed:

• DEFAULT which enables you to specify a default value for the LOB column.

• The LOB_storage_clause, which enables you to specify the LOB storage characteristics for the converted column, can be specified in the MODIFY clause.

Other ALTER TABLE options are not allowed when converting a LONG column to a LOB type column.

If you do not want to use ALTER TABLE, then you can use the TO_LOB operator on a LONG column to copy it to a LOB column. You can use the CREATE TABLE AS SELECT statement or the INSERT AS SELECT statement with the TO_LOB operator to copy data from a LONG column to a CLOB or NCLOB column, or from a LONG RAW column to a BLOB column. For example, if you have a table with a LONG column that was created as follows:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);  

then you can do the following to copy the column to a LOB column:

CREATE TABLE Lob_tab (id NUMBER, clob_col CLOB);  
INSERT INTO Lob_tab SELECT id, TO_LOB(long_col) FROM long_tab;  
COMMIT;

If the INSERT returns an error (because of lack of undo space), then you can incrementally migrate LONG data to the LOB column using the WHERE clause. After you ensure that the data is accurately copied, you can drop the original table and create a view or synonym for the new table using one of the following sequences:

DROP TABLE Long_tab;  
CREATE VIEW Long_tab (id, long_col) AS SELECT * from Lob_tab; 

or

DROP TABLE Long_tab;  
CREATE SYNONYM Long_tab FOR Lob_tab;

This series of operations is equivalent to changing the data type of the column Long_col of table Long_tab from LONG to CLOB. With this technique, you have to re-create any constraints, triggers, grants and indexes on the new table.

Use of the TO_LOB operator is subject to the following limitations:

• You can use TO_LOB to copy data to a LOB column, but not to a LOB attribute of an object type.

• You cannot use TO_LOB with a remote table. For example, the following statements do not work:

  INSERT INTO tb1@dblink (lob_col) SELECT TO_LOB(long_col) FROM tb2; 
  INSERT INTO tb1 (lob_col) SELECT TO_LOB(long_col) FROM tb2@dblink; 
  CREATE TABLE tb1 AS SELECT TO_LOB(long_col) FROM tb2@dblink; 
  

• The TO_LOB operator cannot be used in the CREATE TABLE AS SELECT statement to convert a LONG or LONG RAW column to a LOB column when creating an index organized table.

  To work around this limitation, create the index organized table, and then do an INSERT AS SELECT of the LONG or LONG RAW column using the TO_LOB operator.

• You cannot use TO_LOB inside any PL/SQL block.

Parallel Online Redefinition

On a system with sufficient resources for parallel execution, redefinition of a LONG column to a LOB column can be executed in parallel under the following conditions:

In the case where the destination table is non-partitioned:

• The segment used to store the LOB column in the destination table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled, which is now the default.

• There is a simple mapping from one LONG column to one LOB column, and the destination table has only one LOB column.

In the case where the destination table is partitioned, the normal methods for parallel execution for partitioning apply. When the destination table is partitioned, then online redefinition is executed in parallel.

Example of Online Redefinition

The following example demonstrates online redefinition with LOB columns.

REM Grant privileges required for online redefinition.
GRANT execute ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;

REM Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;

connect pm/passwd

drop table cust;
create table cust(c_id   number primary key,
                  c_zip  number,
                  c_name varchar(30) default null,
                  c_long long
                  );
insert into cust values(1, 94065, 'hhh', 'ttt');

-- Creating Interim Table 
-- There is no requirement to specify constraints because they are 
-- copied over from the original table.
create table cust_int(c_id   number not null,
                  c_zip  number,
                  c_name varchar(30) default null,
                  c_long clob
                  );

declare
 col_mapping varchar2(1000);
BEGIN
--  map all the columns in the interim table to the original table
 col_mapping :=
               'c_id             c_id  , '||
               'c_zip            c_zip , '||
               'c_name           c_name, '||
               'to_lob(c_long)   c_long';

dbms_redefinition.start_redef_table('pm', 'cust', 'cust_int', col_mapping);
END;
/

declare
 error_count pls_integer := 0;
BEGIN
  dbms_redefinition.copy_table_dependents('pm', 'cust', 'cust_int',
                                          1, true, true, true, false,
                                          error_count);

  dbms_output.put_line('errors := ' || to_char(error_count));
END;
/

exec  dbms_redefinition.finish_redef_table('pm', 'cust', 'cust_int');

-- Drop the interim table
drop table cust_int;

desc cust;

-- The following insert statement fails. This illustrates 
-- that the primary key constraint on the c_id column is 
-- preserved after migration. 

insert into cust values(1, 94065, 'hhh', 'ttt');

select * from cust;

Indexes on any column of the table being migrated must be manually rebuilt after converting any LONG column to a LOB column. This includes function-based indexes.

Any function-based index on a LONG column is unusable during the conversion process and must be rebuilt after converting. Application code that uses function-based indexing should work without modification after converting.

Note that, any domain indexes on a LONG column must be dropped before converting the LONG column to LOB column. You can rebuild the domain index after converting.

To rebuild an index after converting, use the following steps:

For applications using anchored types, some overloaded variables resolve to different targets during the conversion to LOBs. For example, given the procedure p overloaded with specifications 1 and 2:

procedure p(l long) is ...;       -- (specification 1)  
procedure p(c clob) is ...;       -- (specification 2)  

and the procedure call:

declare  
     var  longtab.longcol%type;  
   BEGIN  
     ...  
   p(var);  
     ...  
END;  

Prior to migrating from LONG to LOB columns, this call would resolve to specification 1. Once longtab is migrated to LOB columns this call resolves to specification 2. Note that this would also be true if the parameter type in specification 1 were a CHAR, VARCHAR2, RAW, LONG RAW.

If you have migrated you tables from LONG columns to LOB columns, then you must manually examine your applications and determine whether overloaded procedures must be changed.

Some applications that included overloaded procedures with LOB arguments before migrating may still break. This includes applications that do not use LONG anchored types. For example, given the following specifications (1 and 2) and procedure call for procedure p:

procedure p(n number) is ...;       -- (1)  
procedure p(c clob) is ...;         -- (2)  
  
p('123');                 -- procedure call 

Before migrating, the only conversion allowed was CHAR to NUMBER, so specification 1 would be chosen. After migrating, both conversions are allowed, so the call is ambiguous and raises an overloading error.