Change LONG type columns to CLOB type for table redefinition (Doc ID 1391692.1)

Goal

From our Oracle DBA we have received a request that concerns the transition of some LONG type columns to CLOB type columns.

Basically what we want to do is to shrink the physical size of the Oracle Tables by performing a redefinition.

This way, we see performance improvement, decrease of storage and backup size shrinkage.

Apparently the LONG type columns do conflict with this procedure and looking at Oracle Database Concepts 10g we quote:

Oracle recommends that you always use LOB data types over LONG data types.

As there are already some #30 CLOB columns in the Siebel schema, we would expect that this won't give a problem Database wise.

For now we are investigating the following tables that are in use by our Siebel Application:

STATUS - TABLE_NAME - COLUMN_NAME - DATA_TYPE
Table Used - S_EVT_MAIL - EMAIL_BODY - LONG
Table Used - S_ORG_EXT - DIRECTIONS - LONG
Table Used - S_PROD_INT_X - X_LONG_DESC - LONG

Could you help us to see if this is a feasible step to perform, and of course we need Siebel's approval.

We are indeed going to include these modifications in our Test cases and will thoroughly test the behaviour.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.