Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ali.

Asked: September 28, 2002 - 11:22 am UTC

Last updated: October 24, 2017 - 3:16 am UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Hi Tom.
We are designing an office automation application and we want to store image of letters and documents.Our organization is very large (it has 100 departments) and We have to use replications.
Now we want to know is BLOB a proper type for storing these images?
What about BFile? How does oracle handle bfiles replication, backup, access restriction and security? What about data consistency?
Thank's for your reply.

and Tom said...

If you want

o replication
o backup
o consistency
o access restriction
o security

you'll be using a BLOB and only a BLOB. Blobs are in the database and all of the features of the database are available.

A bfile is a file in the OS, outside the database. Use a bfile and you are on your own for all of the above.

Me, I use blobs exclusively for document storage.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 30, 2002 - 4:25 am UTC


My question

Bill, April 10, 2003 - 2:14 pm UTC

We are working on an application that users will be uploading about 250 10MB per day into our database. We are reading the first few lines of the file when it is upload, but other than that we don't use them. All we are doing is storing these files and distributing them to other users. Then after 45 days the files are archived. We had originally deicded to use BLOBs but we got some advice to use BFILEs. Here is that advice making the case for BLOBs and BFILEs acorrding to the circumstances:


> It is always better (if you can) to use BLOBs because of
> following reasons:
>
> 1. Faster access for smaller files (few hundred KBs).
>
> 2. Database security. For BFILEs, OS admin has to maintain
> the security of files. BFILEs are stored outside oracle
> database and it becomes OS admin's responsibility to make
> sure that the files are not overwritten or modifed by someone else.
> For BLOBs, this is not a problem as files are bound by
> database user access security.
>
> 3. Backup and Recovery is easy in case of BLOBs. BFILEs are not
> automatically backed up when you backup the database. It is
> an extra step of backup and admins have to make sure that
> location (pointer) integrity is maintained while restoring
> such an application with BFILEs.
>
> 4. There are more features which can be used in case of BLOBs.
> You can easliy modify the content of file in BLOB. I do not
> think this applies to you - if you are looking for store and
> retrieve kind of application.
>
> So, when do we use BFILEs?? - if you have following considerations:
>
> 1. BFILEs keep the transaction size smaller - hence smaller redo logs
> hence better overall database performance.
> So, for files larger in size (I think this applies to you) it is
> advisable to use BFILEs. A BLOB with large size will cause lot of
> log activity - slowing the transaction performance.
>
> 2. Continuing on the first point - since the database size is smaller
> (only the file pointer is stored), it helps in faster backup and
> recovery of the database. Please do not confuse this point with
> point#3 mentioned above. That is about "easier" backup and recovery
> for admins, this one is about "faster" backup and recovery.
>
> If you plan to use BLOBs in your case, I would recommend that you increase > the "log_buffer" size to few MBs.

If we use BLOBs, it sounds like the issue with the redo log would create a performance problem for us. What do you think?

Tom Kyte
April 11, 2003 - 8:01 am UTC

it depends on your needs.

It is day 1. You load up your files. One day 2, your disk crashes.

What do you do? do you care you lost the files? if not, file system may be ok for you. If the lost data causes you a problem -- sounds like you need a database.


o security
o backup, recovery
o single point of control (no chance someone will "accidently" erase the files)

More on uploading to Blobs

Ray White, June 04, 2003 - 8:04 am UTC

Tom we are using blobs vs Bfiles as per your advise. I agree with the manageability aspects of this decision.

Can you shed some light on how to manage a site where you need to upload files like resumes and spreadsheets that could potentially contain viruses. IE how to detect, remove, prevent the spread of...

How could this be effectively managed using mod/plsql, oracle 9.2 and blobs?

Tom Kyte
June 04, 2003 - 8:38 am UTC

if you use "ifs" (part of collaboration suite) -- it has this sort of software builtin.

otherwise, you would need to get virus checking software from somewhere and use a java stored procedure/external procedure in C to do it yourself. No personal experience myself in that area.

Virus scan a Blob

Ray, June 05, 2003 - 7:55 am UTC

Ok Tom

Understanding that you have no previous expertise in this area. I would really appreciate your opinion or stab or shot in the dark at how you would go about this.

My 2cents
Most readily available and possibly approved by an organization virus software is aimed at file servers. IE not BLOBs. Could a BFILE be advantageous here.
or
a possible process.
Receive the Blob
Flag the Blob as not tested
write the Blob out to a testme directory
launch external virus scanner
determine result
flag blob as clean or dirty
remove file
...


Tom Kyte
June 05, 2003 - 8:39 am UTC

that would work (the second thing)

Bfile vs BLOB

Dhananjay sondur, October 20, 2017 - 3:52 am UTC

HI Tom,

when using Bfile datatype and using bfile in application.How oracle reads bfile data. Is the bfile content will reside at SGA during data fetching by user or os memory will handle all?

Is the Oracle buffer cache(SGA) bypassed when the OracleDB acesse to a Linux file as BFILE?

Thanks & regards,
Dhananjay
Connor McDonald
October 21, 2017 - 1:33 am UTC

It is basically a read out to the OS. We don't need the buffer cache - you can see it is done via a different read mechanism to the standard db file sequential read / db file scattered read

SQL> declare
  2    b bfile := bfilename('TEMP','bigfile.dat');
  3    l_raw   raw(1000);
  4    rnd     int;
  5  begin
  6    dbms_lob.fileopen(b, dbms_lob.file_readonly);
  7
  8    for i in 1 .. 100000
  9    loop
 10      rnd := dbms_random.value(1,1000);
 11      l_raw := DBMS_LOB.substr(b, 1000, rnd);
 12    end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select EVENT
  2  ,TOTAL_WAITS
  3  ,TOTAL_TIMEOUTS
  4  ,TIME_WAITED/100 SECS
  5  ,max_wait
  6  from v$session_event
  7  where sid = sys_context('USERENV','SID')
  8  and event not like 'SQL*Net%';

EVENT                                        TOTAL_WAITS TOTAL_TIMEOUTS       SECS
-------------------------------------------- ----------- -------------- ----------
...
db file sequential read                               89              0        .03
db file scattered read                                 1              0          0BFILE read                                        100000              0        .77
...




BLOb vs BFILE

Dhananjay Sondur, October 22, 2017 - 11:12 am UTC

Hi Tom,

Thanks a lot for the clear example.
could you please elaborate with some examples how FILESYSTEMIO_OPTION can relate with BFILE datatype and which argument is best at parameter FILESYSTEMIO_OPTION=none|SETALL|ASYNC for the BFILE Datatype.

Yours Sincerely,
Posh Kanta

Connor McDonald
October 24, 2017 - 3:16 am UTC

I don't know how far the "reach" of FILESYSTEMIO_OPTION is, ie, if it just datafile operations, or all forms of I/O from the database, and hence whether it applies to BFILE reads.

But even if I *could* give you a definitive answer, I would still recommend testing yourself with either truss or strace, because there are so many variables in play.

For example, if you had a file system mounted as "forcedirectio", or is being accessed via DirectNFS etc, then the decision on how to perform I/O is "taken away" from the database anyway.


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here