This chapter describes techniques for migrating tables that use LONG
datatypes to LOB datatypes. This chapter contains these topics:
Using utldtree.sql to Determine Where Your Application Needs Change
Migrating Applications from LONGs to LOBs
See Also: The following chapters in this book describe support for LOB datatypes in various programming environments: |
There are many benefits to migrating table columns from LONG
datatypes to LOB datatypes.
Note: You can use the techniques described in this chapter to do either of the following:
Unless otherwise noted, discussions in this chapter regarding "LONG to LOB" conversions apply to both of these datatype conversions. |
The following list compares the semantics of LONG and LOB datatypes in various application development scenarios:
The number of LONG
type columns is limited. Any given table can have a maximum of only one LONG
type column. The number of LOB type columns in a table is not limited.
You can use the data interface for LOBs to enable replication of tables that contain LONG
or LONG
RAW
columns. Replication is allowed on LOB columns, but is not supported for LONG
and LONG
RAW
columns. The database omits columns containing LONG
and LONG
RAW
datatypes from replicated tables.
If a table is replicated or has materialized views, and its LONG
column is changed to LOB, then you may have to manually fix the replicas.
Caution: Converting LOB datatypes back to LONG datatypes is not supported. Ensure that you do not need to maintain any column as a LONG datatype before converting the column to a LOB type. |
This section describes preconditions that must be met before converting a LONG
column to a LOB column.
See Also: "Migrating Applications from LONGs to LOBs" before converting your table to determine whether any limitations on LOB columns will prevent you from converting to LOBs. |
Any domain index on a LONG
column must be dropped before converting the LONG
column to LOB column. See "Indexes on Columns Converted from LONG to LOB Datatypes" for more information.
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):
ALTER TABLE Long_tab NOLOGGING;
ALTER TABLE Long_tab MODIFY (long_col CLOB [DEFAULT <
default_val
>]) LOB (long_col) STORE AS (NOCACHE NOLOGGING);
Note that you must also specify NOCACHE
when you specify NOLOGGING
in the STORE AS
clause.
ALTER TABLE Long_tab MODIFY LOB (long_col) (CACHE);
ALTER TABLE Long_tab LOGGING;
Make a backup of the tablespaces containing the table and the LOB column.
You can use the utility, rdbms/admin/utldtree.sql
, to determine which parts of your application require rewriting when you migrate your table from LONG to LOB column types. This utility enables you to recursively see all objects that are dependent on a given object. For example, you can see all objects which depend on a table with a LONG
column. You will only see objects for which you have permission.
Instructions on how to use utldtree.sql
are documented in the file itself. Also, utldtree.sql
is only needed for PL/SQL. For SQL and OCI you do not need to change your applications.
This section describes the following techniques for migrating existing tables from LONG
to LOB datatypes:
Online Redefinition of Tables with LONG Columns where high availability is critical
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 datatype CLOB
using following ALTER
TABLE
statement:
ALTER TABLE Long_tab MODIFY ( long_col CLOB );
Note: TheALTER TABLE 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. |
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.
General issues concerning migration include the following:
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, then the default value for the LONG
column becomes the default value of the LOB column.
Most of the existing triggers on your table are still usable, however UPDATE OF
triggers can cause issues. See "Migrating Applications from LONGs to LOBs" for more details.
If you do not want to use ALTER
TABLE
, as described earlier in this section, 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 datatype 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 will 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.
Tables with LONG
and LONG
RAW
columns can be migrated using online table redefinition. This technique is suitable for migrating LONG columns in database tables where high availability is critical.
To use this technique, you must convert LONG
columns to LOB types during the redefinition process as follows:
Any LONG
column must be converted to a CLOB
or NCLOB
column.
Any LONG
RAW
column must be converted to a BLOB
column.
This conversion is performed using the TO_LOB()
operator in the column mapping of the DBMS_REDEFINITION.START_REDEF_TABLE()
procedure.
Note: You cannot perform online redefinition of tables withLONG or LONG RAW columns unless you convert the columns to LOB types as described in this section. |
General tasks involved in the online redefinition process are given in the following list. Issues specific to converting LONG
and LONG
RAW
columns are called out. See the related documentation referenced at the end of this section for additional details on the online redefinition process that are not described here.
Create an empty interim table. This table will hold the migrated data when the redefinition process is done. In the interim table:
Define a CLOB
or NCLOB
column for each LONG
column in the original table that you are migrating.
Define a BLOB
column for each LONG
RAW
column in the original table that you are migrating.
Start the redefinition process. To do so, call DBMS_REDEFINITION.START_REDEF_TABLE
and pass the column mapping using the TO_LOB
operator as follows:
DBMS_REDEFINITION.START_REDEF_TABLE( 'schema_name', 'original_table', 'interim_table', 'TO_LOB(long_col_name) lob_col_name', 'options_flag', 'orderby_cols');
where long_col_name
is the name of the LONG
or LONG
RAW
column that you are converting in the original table and lob_col_name
is the name of the LOB column in the interim table. This LOB column will hold the converted data.
Call the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
procedure as described in the related documentation.
Call the DBMS_REDEFINITION.FINISH_REDEF_TABLE
procedure as described in the related documentation.
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 privleges 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/pm 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 need 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;
See Also: The following related documentation provides additional details on the redefinition process described earlier in this section:
|
This section discusses differences between LONG
and LOB datatypes that may impact your application migration plans or require you to modify your application.
Most APIs that work with LONG datatypes in the PL/SQL and OCI environments are enhanced to also work with LOB datatypes. These APIs are collectively referred to as the data interface for persistent LOBs, or simply the data interface. Among other things, the data interface provides the following benefits:
Changes needed are minimal in PL/SQL and OCI applications that use tables with columns converted from LONG
to LOB datatypes.
You can work with LOB datatypes in your application without having to deal with LOB locators.
See Also:
|
LOB columns are not allowed in clustered tables, whereas LONG
s are allowed. If a table is a part of a cluster, then any LONG
or LONG
RAW
column cannot be changed to a LOB column.
You cannot have LOB columns in the UPDATE
OF
list of an UPDATE
OF
trigger. LONG
columns are allowed in such triggers. For example, the following create trigger statement is not valid:
create table t(lobcol CLOB); create trigger trig before/after update of lobcol on t ...;
All other triggers work on LOB columns.
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 will be 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:
Select the index from your original table as follows:
SELECT index_name FROM user_indexes WHERE table_name='LONG_TAB';
Note: The table name must be capitalized in this query. |
For the selected index, use the command:
ALTER INDEX <index> REBUILD
A LOB column can hold an empty LOB. An empty LOB is a LOB locator that is fully initialized, but not populated with data. Because LONG
datatypes do not use locators, the "empty" concept does not apply to LONG
datatypes.
Both LOB column values and LONG
column values, inserted with an initial value of NULL
or an empty string literal, have a NULL
value. Therefore, application code that uses NULL
or zero-length values in a LONG
column will function exactly the same after you convert the column to a LOB type column.
In contrast, a LOB initialized to empty has a non-NULL
value as illustrated in the following example:
CREATE TABLE long_tab(id NUMBER, long_col LONG); CREATE TABLE lob_tab(id NUMBER, lob_col CLOB); INSERT INTO long_tab values(1, NULL); REM A zero length string inserts a NULL into the LONG column: INSERT INTO long_tab values(1, ''); INSERT INTO lob_tab values(1, NULL); REM A zero length string inserts a NULL into the LOB column: INSERT INTO lob_tab values(1, ''); REM Inserting an empty LOB inserts a non-NULL value: INSERT INTO lob_tab values(1, empty_clob()); DROP TABLE long_tab; DROP TABLE lob_tab;
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 will resolve 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.
PL/SQL permits implicit conversion from NUMBER
, DATE
, ROW_ID
, BINARY_INTEGER
, and PLS_INTEGER
datatypes to a LONG
; however, implicit conversion from these datatypes to a LOB is not allowed.
If your application uses these implicit conversions, then you will have to explicitly convert these types using the TO_CHAR
operator for character data or the TO_RAW
operator for binary data. For example, if your application has an assignment operation such as:
number_var := long_var; -- The RHS is a LOB variable after converting.
then you must modify your code as follows:
number_var := TO_CHAR(long_var); -- Assuming that long_var is of type CLOB after conversion
The following conversions are not supported for LOB types:
BLOB
to VARCHAR2
, CHAR
, or LONG
CLOB
to RAW
or LONG
RAW
This applies to all operations where implicit conversion takes place. For example if you have a SELECT
statement in your application as follows:
SELECT long_raw_column INTO my_varchar2 VARIABLE FROM my_table
and long_raw_column
is a BLOB
after converting your table, then the SELECT
statement will produce an error. To make this conversion work, you must use the TO_RAW
operator to explicitly convert the BLOB
to a RAW
as follows:
SELECT TO_RAW(long_raw_column) INTO my_varchar2 VARIABLE FROM my_table
The same holds for selecting a CLOB
into a RAW
variable, or for assignments of CLOB
to RAW and BLOB
to VARCHAR2
.