The recent discovery of a LONG column in a table used by a business critical application cause me cause to stop and consider my approach. The scenario was that the new application was based on communication layers that would not support LONG column data. The old application would not support CLOB data, so I would have to change the column at the point of going live with the new app.
I have serious dislike for LONG columns. It's not at the level of the total revulsion and outrage I feel for WHEN OTHERS THEN NULL, but it is significant. LONGs are incredibly restricting with regard to what you can do with them and difficult to work with as a result.
They can be easily dealt with, however. How about...
ALTER TABLE mytable MODIFY(mycolumn CLOB);
It's does have a couple of benefits.
Job done!? Well... it's the LONG column we want to say goodbye to, not our career, so what's the backout plan? What if something goes horribly wrong during the operation? What if the database change is made successfully, but we have to fall back to the old application for other reasons?
That command is a one way trip! There is no way to alter it back. Flashback won't help because its DDL.
So what tools do we have in the box, besides the tempting ALTER command above?
The standard approach would be to create a duplicate table using the CREATE TABLE...AS..(OR INSERT INTO)... SELECT... TO_LOB()... command. (This is a special case where we are allowed to use the TO_LOB() function to generate a LOB from a LONG (or LONG RAW).
We could replicate the table (with a CLOB instead of a LONG) and all the table's orbital objects (indexes, triggers, etc.), then RENAME the original table to a backup name and RENAME the new table to the original name. After a recompile of dependent code, everything should be good again.
(Additional note: The RENAME command prevents the use of a schema name for the object, so must be run as the owner of the object. The ALTER TABLE <schema>.<table> RENAME TO <new name> syntax can be run from a privileged account, making automation simpler - many thanks to the DBA team for this tip).
We also have the option of recovering from a Data Pump export file if the process fails. this will require some downtime for operations which depend on the table in question. It's also worth testing the recovery process before altering the database. We need to know that recovery is reliable and we know operations with LONG columns aren't always supported.
The CREATE TABLE command (with related objects) is still the most appealing approach. While it requires additional space (if the DBAs can provide it), it has the following benefits:
Points Worth Noting