cardel wrote:How does that save space?
Hello, I have Oracle APEX application running on 11g XE database.
I want to save some space in DB so I decided to store files (images) outside of database.
But I don't want to have all files in one directory and I have this directory structure.To what purpose? What problem does that solve?
Every filename is transformed to SHA1 hash and hash is used to define main directory and subdirectory where file will be stored.
file.jpg -> sha1 -> 75667EA46F0ACC8ED33FB6F5EBC5CD462ED3B2A4.jpg will be stored as
file1.jpg -> sha1 -> 52E9A7A18BD5077BA2C45BAC16D6FB59397E515D.jpg will be stored as
So under BFILES main directory I can have max 256 subdirectories and every subdir can have max. 256 dirs
With this technique files will be stored equally and not in only one directory.
But I have one problem with this approach and with accessing files in subdirectories with UTL_FILE (same problem as mentioned here: UTL_FILE - reading file in subdirectory )Rethink your "objective". Ask yourself what business problem you think it solves. Why do you think you are 'saving space' by storing the files as files outside of the db rather than as blobs? Even making that dubious decision, why do you think there is an advantage to saving the files in multiple directories?
UTL_FILE can not read/write data to subdirectories of main Oracle Directory
I am able to acces (read/write) files in main directory c:\oraclexe\bfiles\ that is defined as Oracle Directory object, but I am not able to write to any of it subdirectories.
If I create Oracle Directory object for my every subdir (\75\66\, \52\E9\ ...) I would be able to write my files there. But it will result into 65536 directory objects and I would like to do it better.
Do you have some ideas?
Ideally only one main Oracle Directory and access to all of its subdirs.Ideally, store the .jpg files as blobs within the database.
cardel wrote:You said it yourself: "UTL_FILE can not read/write data to subdirectories of main Oracle Directory"
11g XE has limited tablespace to 11GB so files will be stored outside database to save tablespace space.
This is my decision and I don't want to start discussion what is better filesystem or database. My files will be stored outside database.
Hashing filenames and storing them in subdirectories is for performance reason. I don't want to have thousands files in one folder and also windows doesn't like it. Try to work with folder that contains 100 000 images with for example windows explorer...
I would like to read some tips how to access files in subdirectories with UTL_FILE.
Edited by: cardel on Dec 23, 2012 11:53 AM