LONG to LOB Migration

How Can We Migrate LONGs to LOBs, If Our Application Cannot Go Down?

Question

Our current table consists of records with 3 fields - a sequence, a redundancy check number, and a long raw field. The size of the long raw field is around 100KB but it can be as big as 300KB. The entire file is 160GB and the server's maximum size is 200GB. We converted this database from 7.3.4 to 8.1.6 and now our application programs do not work well with the LONG raw fields. We want to convert them to BLOBs. We cannot have the application down while we migrate to BLOBs. What suggestions do you have?

Answer

Oracle9i allows you to use ALTER TABLE in order to copy the data from a LONG to a LOB. See Chapter 8, "Migrating From LONGs to LOBs". But the ALTER TABLE command would make the table unusable for the duration of the ALTER.

Another way to do this is to use the TO_LOB operator introduced in Oracle 8i to copy data from the LONG to the LOB. You can take a look at the Oracle8i Migration Manual, Chapter 8 -- Copy LONGs to LOBs. In this case, the table will be unusable for a much shorter duration of time.

See Also: Chapter 8, "Migrating From LONGs to LOBs"