[SAK-7977] Change fields from long to clob for Oracle Created: 07-Oct-2006  Updated: 30-Jun-2014  Resolved: 14-Feb-2010

Status: CLOSED
Project: Sakai
Component/s: Database service (Pre-K1/2.6)
Affects Version/s: 2.2.0, 2.2.1, 2.2.2, 2.2.3, 2.3.0, 2.3.1, 2.3.2, 2.4.0, 2.4.1, 2.5.0
Fix Version/s: None

Type: Task Priority: Major
Reporter: John Leasia (Inactive) Assignee: Unassigned
Resolution: Incomplete Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


This relates to REQ-103, where we can't use the Site browser tool due to performance problems, and other performance issues. In particular, it is very difficult to search and do anything with the properties currently buried in xml fileds in certain tables. A conversion to use the clob type would help, as well as moving some fields to their own column (e.g., the term field, which is used very frequently).

some background:

On Thu, Aug 10, 2006 at 02:49:25PM -0400, Drew Zhu wrote:

> All,
> As I suggested before, we should switch from LONG to CLOB for the
> columns in the databases because Oracle started this suggestion from 8i
> and a lot of Oracle features do not support LONG. As far as I know, even
> MySql supports CLOB datatype. Two most recent events confirm me we
> should convert from LONG to CLOB.
> 1. There some block fragmentations in production database and I want to
> use Oracle 10g's new feature, shrink space to reclaim space and do
> defragmentation because the fragmentations cause IOs and waits, and
> therefore, slower the database performance. I had some successes.
> However, when a table contains LONG column, I cannot do it. The
> following is an example of the error (we have more than 10 errors like
> this):
> SQL> alter table "CTOOLS_USER"."CONTENT_RESOURCE" shrink space
> *
> ERROR at line 1:
> ORA-10662: Segment has long columns
> When checking Oracle support for related documentation, I found the
> following:
> "it is a documented restriction that you cannot shrink tables (segments)
> that contain the obsolete long data type. You will have to resort to
> use of the old exp/truncate/imp with ignore=y option."
> That would be huge task which can be done easily by using Oracle 10g's
> "shrink space" method. When we need do that maintenance often, it would
> make a big difference.
> 2. John asked me to delete some unwanted assignments from
> ASSIGNMENT_ASSIGNMENT, which has a LONG datatype, in CTOOLS database.
> That would be a piece of cake if there are no LONG columns. However,
> with the LONG datatype, we cannot search any strings unless we use
> Oracle text, which we currently are not using. Even with Oracle text
> search, we need to a lot of extra work, such as enabling the Oracle text
> feature, build the TEXT indexes, and building search conditions. I
> eventually came with a "workaround" by building a temporary table and
> converting the LONG to CLOB in that temporary table and then do the
> search in the temp table and then compare the two tables and then do the
> deleting. If we have a CLOB, the query would be much easier to
> construct and the task would be much easier.
> Converting LONG to CLOB is not a big task and I have done that before.
> All we need is a key Oracle function, TO_LOB, which is already there
> (yes, it will take some time to write the conversion scripts but we can
> to it one by one). To developers, the change is transparent and they
> do not need to make any changes to their codes. All the applications
> would be the same with LONG substituted by CLOB.
> There more reasons to make the switch. Here I just list what I faced by
> examples so far.
> The above are for your reference.
> Thanks,
> Drew

Comment by Peter A. Knoop [ 05-Oct-2007 ]

LONG is deprecated in Oracle, so we need to switch to CLOB for the next release (2.6). This will involve both conversion scripts and code changes.

Comment by David Horwitz [ 23-Jan-2010 ]

Maintenance team review: If this issue is being worked on please take ownership of it by assigning it to yourself. Otherwise it is likely to be closed as an abandoned issue. If this is not being pursued please close the issue.

Comment by David Horwitz [ 14-Feb-2010 ]

[Bulk Change]: MAINT TEAM REVIEW: this appears to be an abandoned issue if this is still being activly followed please update the information and make sure is is assinged to the developer/team adressing it.

Comment by Earle R Nietzel [ 28-Mar-2013 ]

If someone comes by this issue and creates a script to do this please share it.

Thank you

Comment by David Adams [ 29-Mar-2013 ]

I've cleaned up and generified the script we used to address this issue originally. See:


The script will by default just scan for LONG and LONG RAW fields but will not change any data. See the usage notes for the command line argument needed to convert the LONG fields to LOB fields. This works on my systems, but I can't guarantee it will work for everyone, or for all versions of Oracle (we run or SQL*Plus. Always understand what the script is doing before you try to run it and test it thoroughly on a non-production system with realistic data before attempting it with your production systems.

Generated at Sun Feb 23 11:10:36 CST 2020 using Jira 8.0.3#800011-sha1:073e8b433c2c0e389c609c14a045ffa7abaca10d.