Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Oracle blob

Oracle blob

From: kirschk <>
Date: Thu, 22 Jul 2004 12:39:56 -0400
Message-ID: <>

Someone wrote once:

May be this is a bug in oracle? Here is what I found out.

  1. create a table with blob field and insert a empty blob record. SQL>create table demo(id number, data blob); SQL>insert into demo values(1,empty_blob());
  2. Insert a file of size 1 MB into the blob field using JDBC API. After inserting the file, try reading and verifying the contents.
  3. Now insert another file of size 500kb ( file size should be less than previouly inserted file) into the same record( id=1) blob field.

   When you read this blob, you'll see that blob size is returning a size of 1MB. And the worst thing is, the blob stream in addition to the new file, has old files junk characters in the end.

So once a empty blob is used to store some data, it should not be modified and used to store another set of data. To resolve this issue, I delete the record and insert a new record in order to update the blob field with the new data.

No I have a very similar problem.
I have a oracle table with a blob field. In the blob field are many records of X kb. Now I need to get rid of the X kb,
since the blobs are no more useful, but the rest of the recordset must not be deleted!

If I use null as blob value, then I loose the reference, to other records in other tables.
If I use empty blob, I dont gain free space, which is my goal. (see upper message)

How can I get rid of the blob data, without loosing the structure?

I am talking about 160 Gbyte blob Data, which must be dropped somehow…and also quick, since we are running out of space here, so please answer as soon as possible,

Many many thanks in advance,

kostas Received on Thu Jul 22 2004 - 11:39:56 CDT

Original text of this message