Re: Blobs vs Bfile in 10g. Pros and Cons?

From: Vladimir M. Zakharychev <>
Date: Thu, 12 Nov 2009 07:14:18 -0800 (PST)
Message-ID: <>

On Nov 9, 10:45 pm, Rich <> wrote:
> On Nov 8, 2:40 pm, Shakespeare <> wrote:
> > Rich schreef:
> > > Our department is uses 10g.
> > > We will be loading PDF files and/or pictures into our database.
> > > What are the pros and cons verses Bfile and Blobs. What has been your
> > > experience?
> > > Regards
> > LOBS reside inside your database, and thus are included in your backup,
> > securtity etc. Uploading will take some time.
> > BFiles are pointers to objects outside your database, and will not be
> > backed up with regular database backups. But on the other hand, are
> > easier to read/write woth tools outside the database.
> > It all depends on your business requirements...
> > Shakespeare
> Thanks for the reply.
> I read that LOB's can store 4GB of data. Is that a grand total for the
> database? Or is that per record? For example, if I have 10 records in
> the database does that mean that I can have 40GB of data in the BLOB?

For the sake of manageability, consistency, security, reliability, portability, etc. don't even think about using BFILEs (they have their uses but storing binary data in an Oracle database is not one of them.) LOBs are the way to go as they provide for all of the above and a lot more. Initially loading existing files into the database will take some time, but your effort will surely be rewarded. and it won't take long anyway. There are many ways to accomplish this: SQL*Loader, XML DB, PL/SQL using DBMS_LOB interfaces, some ETL tool that supports LOB mass-loading. Getting them out is easy, too.

My $0,02.

  Vladimir M. Zakharychev
  N-Networks, makers of Dynamic PSP(tm) Received on Thu Nov 12 2009 - 09:14:18 CST

Original text of this message