Skip to Main Content
  • Questions

  • Storing documents Database v/s File system

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Guru.

Asked: June 01, 2003 - 11:07 am UTC

Last updated: July 29, 2003 - 11:23 am UTC

Version: 8.1.7.1

Viewed 1000+ times

You Asked

Hi Tom,
I am considering design options for an application in which a user will upload and store documents and that will be dowloadable by others. The documents would be MS word docs on an avg. 150 K. There would be a migration of 18,000 - 20,000 docs (from CDs) and would rise to abt. 25,000 when the app is used. The browser front end will be PL/SQL cartridge pages for upload and download. Initially the app will accessed by 400 - 500 people (nearly) simultaniously and within 2 weeks it will become 300 people a day (scattered access).
The documents themselves are to be stored in the database in BLOB.
Do you think that is a good option considering the usage?
I am particularly conserned about the impact on SGA. will it not hog the SGA?

SQL> sho sga

Total System Global Area 390458388 bytes
Fixed Size 76820 bytes
Variable Size 340172800 bytes
Database Buffers 49152000 bytes
Redo Buffers 1056768 bytes

considering the app requirements and the front end (browser based) are there other options than storing in BLOB like the File system?

your inputs will be valueable as always... TIA..
guru


and Tom said...

I stored everything in there.

We have a single multi-terabyte instance used as Oracles single file server. All of the documents are in there.

Blobs can be NOCACHE, if you don't want them in the buffer cache -- that is upto you.

If the data means anything to you, has any value whatsoever, you will in fact put it into a database where it is professionally managed, backed up, recoverable, secure.

Rating

  (7 ratings)

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

Comments

Don't forget about Oracle Text

Tyler D. Muth, June 01, 2003 - 6:47 pm UTC

Oracle Text provides a lot of great search features such as themes and "gist". Check it out at:
</code> http://otn.oracle.com/products/text/pdf/9ir2text_features_overview.pdf 
and
http://otn.oracle.com/products/text/content.html <code>


A reader, June 01, 2003 - 10:16 pm UTC

Tom,

I have similar kind of question related to iFS, can you tell me some adavantages & why should i go for it.


Thanks.

Tom Kyte
June 02, 2003 - 7:16 am UTC

single file server for the entire company. no file servers sitting under peoples desks. no "where the heck is that file". totally indexed, searchable. stored in the database -- professionally (one would hope) managed and backed up. content management facilities. accessible via pretty much any network protocol you need. transparent to the end user....



please elaborate..

guru, June 02, 2003 - 3:03 am UTC

"I stored everything in there. " - can you elaborate on this? I can see Oracle Intermedia at work. Are you also storing everything in LOBs?

"We have a single multi-terabyte instance used as Oracles single file server.
All of the documents are in there."
I know about this instance which hosts a lot of Oracle sites.. but I did not understand "used as Oracles single file server." can you please elaborate on this?




Tom Kyte
June 02, 2003 - 7:29 am UTC

yes, everything is in lobs.


Oracle has a single fileserver, we consolidated from hundreds of little NT file servers into a single fileserver for the entire company. Last I checked in october 2002 there was:

55,000+ internal Oracle users
10,500,000+ documents
Over 3.7 TB storage used

it'll be larger by now.


Can you elaborate little more ?

Randy Miller, June 11, 2003 - 2:49 pm UTC

Hello Tom,
Very interesting. Can you elaborate little more how Oracle file server is created ? Are you using Oracle file system or any other technology other than tables in terms of rows and columns ? I am looking for any the specific product if you are using other than only Oracle RDBMS engine.



Tom Kyte
June 11, 2003 - 7:56 pm UTC

it uses tables in the database.

Uploading Files

Ray White, July 29, 2003 - 9:29 am UTC

Hey Tom,

I don't mean to beat a dead quadraped, but...

Using 9.2
Does Mod PLSQL support uploading a file into a BFILE directly, or do you have to upload to a BLOB and write the blob out to a BFILE.
If so...
What do I use to write the Blob out to a BFILE?
What do I use to check the existence of a File?
I have talked to you about this b4. but just getting back to it. I am writing the BFILE , only to have it virus scanned. The virus scan intercepts the write, and if the file exists where I write it, it is clean... Then I can get rid of it.

Tia
Ray

Tom Kyte
July 29, 2003 - 11:10 am UTC

No, file upload to the file system does not exist in mod_plsql.


dbms_lob.fileexists checks for existence.

In 9ir2, utl_file can be used to WRITE to a raw file.
In 9ir1 and before, you would need to use a java stored procedure to do the same.



Upload files

Ray White, July 29, 2003 - 11:23 am UTC

Right on.

Thanks alot.

files

A reader, April 17, 2010 - 1:17 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here