Re: best practice on migrating long RAW to blob

On Sep 7, 12:41 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Am I supposed to see any 'LONG RAW' columns that are in the database in the USER_LOBS table? I ran the 2nd query you listed, and it does not show my LONG RAW column in user_lobs at all.

I ran this query on a "small version" of the database, not the one I had the ORA-01652 error on. Though I think the setup (tablespace-wise, etc) is similar. It just has little records. I won't have access to the ORA-01652 database until Monday, I just wanted to ask above question in the meantime. On Monday, I'll run both queries on the ora-01652 database and post results.

thanks, Received on Fri Sep 07 2007 - 14:45:59 CDT