3 Replies Latest reply: Dec 23, 2012 10:49 AM by EdStevens RSS

    Storing files outside database and access to subdirectories

    cardel
      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.

      Every filename is transformed to SHA1 hash and hash is used to define main directory and subdirectory where file will be stored.

      Example

      file.jpg -> sha1 -> 75667EA46F0ACC8ED33FB6F5EBC5CD462ED3B2A4.jpg will be stored as

      c:\oraclexe\bfiles\75\66\75667EA46F0ACC8ED33FB6F5EBC5CD462ED3B2A4.jpg

      file1.jpg -> sha1 -> 52E9A7A18BD5077BA2C45BAC16D6FB59397E515D.jpg will be stored as

      c:\oraclexe\bfiles\52\E9\52E9A7A18BD5077BA2C45BAC16D6FB59397E515D.jpg

      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 )

      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.
        • 1. Re: Storing files outside database and access to subdirectories
          EdStevens
          cardel wrote:
          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.
          How does that save space?

          But I don't want to have all files in one directory and I have this directory structure.

          Every filename is transformed to SHA1 hash and hash is used to define main directory and subdirectory where file will be stored.

          Example

          file.jpg -> sha1 -> 75667EA46F0ACC8ED33FB6F5EBC5CD462ED3B2A4.jpg will be stored as

          c:\oraclexe\bfiles\75\66\75667EA46F0ACC8ED33FB6F5EBC5CD462ED3B2A4.jpg

          file1.jpg -> sha1 -> 52E9A7A18BD5077BA2C45BAC16D6FB59397E515D.jpg will be stored as

          c:\oraclexe\bfiles\52\E9\52E9A7A18BD5077BA2C45BAC16D6FB59397E515D.jpg

          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.
          To what purpose? What problem does that solve?

          >
          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 )

          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?
          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?
          Ideally only one main Oracle Directory and access to all of its subdirs.
          Ideally, store the .jpg files as blobs within the database.
          • 2. Re: Storing files outside database and access to subdirectories
            cardel
            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
            • 3. Re: Storing files outside database and access to subdirectories
              EdStevens
              cardel wrote:
              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
              You said it yourself: "UTL_FILE can not read/write data to subdirectories of main Oracle Directory"

              If you have the kind of storage requirements you indicate, then I'd say XE is not the proper choice of a platform. Whatever platform you choose, you have to live within the limitations of that platform. XE has limited tablespace size. Oracle (any edition) limits the access to utl_file to the specific named directory. There are no hidden parameters to over-ride either.

              Edited by: EdStevens on Dec 23, 2012 10:47 AM