best practice on migrating long RAW to blob

Our application has a certain table that stores binary data as 'long raw'. We want to change it over to BLOB, so we can use a field that Oracle recommends, etc. We want to keep the data in a table/column with the same name, so we don't have to change our application.

I want advice on the better way to do it. Should I just "modify" as such: ALTER TABLE ODB."BLOB_TABLE" MODIFY ("BLOB_ITEM" BLOB);

Or should we go the route of creating a new column/table, migrating the data, clearing the long raw column and then dropping it, recreating  it as a BLOB, and them put the data back from the new column, and then drop the new column?

We tried the simple modify route, and it works fine on databases with little data in that table. But when doing the same thing on a table with a large amount of records, we've had tablespace errors (ORA-01652: unable to extend temp segment by 8192 in tablespace USERS) which don't make sense to us, because we see the tablespace USERS as having plenty of space (and it is set to auto-extend).

But regardless, what is the best way to do this considering a table that will have a large amount of binay data? The "modify" route is the "simple" way, but doing a migration gives us more control, though it may take longer and eat up more space while it is running.

We're an ISV, so the operation we'll be doing will be done on all our customer's databases.

Appreciate any advice. Received on Fri Sep 07 2007 - 09:13:48 CDT