Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: best practice on migrating long RAW to blob

Re: best practice on migrating long RAW to blob

From: Luch <>
Date: Mon, 10 Sep 2007 13:23:35 -0000
Message-ID: <>

On Sep 7, 8:02 pm, DA Morgan <> wrote:
> Luch wrote:
> > On Sep 7, 12:41 pm, DA Morgan <> 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
> >>> 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
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users
> > 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,
> LONG RAW is not stored as a LOB ... LOBs, almost always, are if you
> are reading Oracle's docs.
> The idea of the queries was to help determine why you were getting
> the error? Seems like you need to post the DDL you were running and
> the full error message (not an edited version) for us to help you
> further.
> --
> Daniel A. Morgan
> University of Washington
> (replace x with u to respond)
> Puget Sound Oracle Users

I think we figured out what problem was... the USERS tablespace file seemed to hit it's limit at 32 gigs. We created another tablespace file for USERS and the modify worked okay.

We'll continue testing, but I'll keep assuming that using a MODIFY to change the column type is the best way to change it for all our customers.

thanks, Received on Mon Sep 10 2007 - 08:23:35 CDT

Original text of this message