LONG to CLOB Conversion in Oracle
We have finished the LONG to CLOB conversions in the University of Michigan (U-M) production database. The process was smooth and yet time consuming. I would like to summarize a few key points for the conversion. Hope it can be helpful to those who have not done the conversions.
1. If you have not done the conversions yet, it’s better to do it earlier than later because the larger the tables to be converted, the longer it needs to do so.
2. Why do the conversion?
Long fields containing XML data could not be searched directly with sql.
An example - there were performance problems trying to find the term a site was associated with because the value for a term was buried in a long field of xml which was difficult to search (which is why we don't currently have the gateway sites tool enabled in U-M). We couldn't search for a particular term in the value field since it was a long filled with xml. Now we could:
select SITE_ID from CTOOLS_USER."SAKAI_SITE_PROPERTY"
where name='term'
and dbms_lob.instr(value, 'FALL 2007',1,1) > 0
3. Choose the correct conversion method. Oracle 10g release 2 provides an Online Data Reorganization & Redefinition feature that can be used for this type of activity. Details can be seen from the following links:
http://www.oracle.com/technology/deploy/availability/pdf/ha_10gR2_online_reorg_twp.pdf
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14210/hafeatures.htm#sthref38
4. U-M did not use the method mentioned above and simply used the “Alter table table_name modify column_name CLOB”. That was because we were told the application servers have to be restarted after the conversion, and during the test conversion, the conversion method we used worked fine and quickly. That may not be a good choice because we needed significant downtime to finish the conversion. You should try the method mentioned above first if your production database is large.
5. The procedure used for U-M LONG to CLOB conversion (login as schema owner) can be seen below. This procedure works only for Oracle 9i or higher.
begin
for tab in (select table_name,column_name from user_tab_columns where data_type='LONG') loop
execute immediate 'alter table '||tab.table_name||' modify '||tab.column_name||' clob';
for ind in (select index_name from user_indexes where table_name=tab.table_name) loop
begin
execute immediate 'alter index '||ind.index_name||' rebuild online tablespace ctools_indexes';
exception
when others then
null;
end;
end loop;
execute immediate 'analyze table '||tab.table_name||' compute statistics';
----Note: Can also use DBMS_STATS package
end loop;
end;
/
6. The conversion is done one table a time and can be done online (or live) without affecting those applications that do not use the table being converted. If you want to do the conversion when applications are live, you need to test it in test database and make sure it works for you. Otherwise, the conservative way, stop the application and restart the application servers, is recommended.
7. The conversion is time consuming. It took 10 hours altogether to convert all the 26 tables with LONG in U-M production tables. For large tables, such as CONTENT_RESOURCE and CONTENT_COLLECTION, it took more than one hour to convert a single table in a database that is hosted on a T2000 Sun-Fire.
8. CLOB takes more disk space. The CLOB doubles the space usage for a table. In our case, the total datafile size increased from 95 G to 124 G after the conversion (just the data and index part for all the tables, not including sys, temp, uodo tablesapaces). The backup will also need more spaces.
9. The conversions needed temporary space to hold the data during the conversion. During the conversion process, Oracle needs space to hold intermediate data that have been converted before all the data have been converted.
10. The operation generated lot of archived logs. In U-M’s case, it generated 3 to 4 logs of 100M every minute. This, again, consumed a lot of disk spaces.
11. The flash_recovery_area also needed large space. In our case, the size of this area reached 80 gigs after the conversions. So, other than sufficient disks, remember to set the parameter DB_RECOVERY_FILE_DEST_SIZE larger than in normal operation if the flashback feature is on.
12. The conversion can be terminated at any given time. During the conversion using “Alter table …”, the converted data are not committed until the conversion finishes. Stop/kill the conversion in the middle of the conversion will rollback the conversion and LONG data will be preserved and the column will still be LONG. The application will not be affected by terminating the conversion. This is table based operation. Those tables that have been converted will not be rolled back.
13. Last but not least, do tests with the table sizes equal or close to what’s in production database to get a better sense of the conversion process. Monitor the size growth, the undo, temp and all related tablespace growths during the test.