Re: best practice on migrating long RAW to blob

Luch wrote:
> 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, re-
> creating 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).

If you've run a test that works and the only issue is the inability to extend then run the following and post the results:

SELECT dbms_metadata.get_ddl('TABLE', '<your_table_name_here>') FROM dual;

SELECT table_name, column_name, tablespace_name FROM user_lobs;

My guess is that the table is in USERS but the LOB is not.

Also check you schema's quota on the USERS tablespace and room available on disk.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org