In Oracle8i BLOB's (Binary Large Objects) can be used instead of LONG RAW's to store binary unformatted data, like documents, images, audio and video. On the new BLOB data type many of the former LONG RAW restrictions are not valid anymore and up to 4GB can be stored. This tip shows how to migrate LONG RAW columns to BLOB's.

It is worth to create a separate tablespace for the LOB's bigger contents to gain performance. The tables containing LOB columns can be stored together with other tables in a tablespace (called tab in this sample). However the LOB columns referencing their data in a separate tablespace (called btab here).

CREATE TABLESPACE btab
DATAFILE '.../lob/POR1_lob1.dbf' SIZE 512064K REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
PERMANENT
ONLINE;

A new table must be created that contains the new BLOB column. Even if it is possible to add a BLOB column to an existing table we cannot migrate old LONG RAW data in it. The required SQL function TO_LOB can be used in SELECT subqueries of INSERT statements only.

Lets assume the old table docs looks like this...

id NUMBER(10) NOT NULL
bdata LONG RAW NULL

... we create the new one:

CREATE TABLE newdocs (
id NUMBER(10) CONSTRAINT nn_newdocs_id NOT NULL,
bdata BLOB DEFAULT empty_blob() NULL
)
PCTFREE 5
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE tab STORAGE (
INITIAL 500K
NEXT 500K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
)
LOB (bdata) STORE AS (
TABLESPACE btab
STORAGE (INITIAL 100M NEXT 100M PCTINCREASE 0)
CHUNK 50
PCTVERSION 10
NOCACHE LOGGING
);

LOB's in general do not use rollback segments. To maintain read consistency Oracle creates new LOB page versions every time a lob changes. PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data. The PCTVERSION can be set to the percentage of LOB's that are occasionally updated. If LOB's are inserted once and afterwards usually read only, 0% can be used.

If CACHE is specified Oracle places LOB pages in the buffer cache for faster access. NOCACHE can be used if there are occasionally no writes to stored LOB's and infrequently reads only. CACHE READ is good for busy read operations and infrequent writes.

Set CHUNK to the number of blocks of LOB data that will be accessed at one time. This reduces network roundtrip overheads. The INITIAL and NEXT storage parameters must be greater than CHUNK * DB_BLOCK_SIZE size. Use bigger CHUNK's if possible.

The default setting ENABLE STORAGE IN ROW stores LOB's less than 4KB within the table and greater LOB's are automatically moved out of the row. This is the recommended setting. DISABLE STORAGE IN ROW can be used to store all data outside the rows. A lot of small LOB's within a table can decrease performance of table operations like full table scans or multi-row accesses.

Consider that CHUNK and ENABLE/DISABLE STORAGE IN ROW cannot be altered after table creation.

Finally we can use the following SQL statement to migrate the data from the old to the new table:

INSERT INTO newdocs (id, bdata)
SELECT id, TO_LOB(bdata)
FROM docs;

To copy the data is easy. The SQL function TO_LOB( ) can be used to convert LONG RAW to BLOB. It's also possible to convert LONG to CLOB if required. The main thing of the whole data migration is to choose good storage parameter settings especially if a large number and large LOB's in size need to be stored.