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

Last updated on MARCH 02, 2017

Applies to:

Siebel Media Sales - Version 8.0.0.9 SIA[20433] and later
Information in this document applies to any platform.
***Checked for relevance on 28-Jan-2014***

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms