We have some APEX applications for our colleagues, where people can attach files (usually MS Word documents or PDF files).  A straightforward way to implement it was to use a table with BLOB field and connect it with the file item in an upload form.  All works well,  but as times goes on BLOBs are starting to take significant amount of space.    I have been looking for some solution, how to reduce space taken by attached documents and explored Oracle 11g compression capabilities.
For LOBs 11g (Enterprise Edition)  provides new feature called SecureFiles, which also enables compression of LOB data. We have migrated our tables to use SecureFiles and saved approximately  50% of space.

By default LOBs are still saved by usual way (BasicFiles),  a table must be created  with special options to enable SecureFiles and compression of data.   So I had to migrate our existing BLOB tables to use SecureFiles. Unfortunately this cannot be done easily by ALTER TABLE command.  There are basically two possibilities:

• Create new table with same structure, migrate data and then rename it back to original name –
  This has to be done offline. Advantage of this approach is that you do not need any special additional rights for the database and steps are quite straightforward.  Obvious disadvantage is that you must close you application for a while, until you recreate appropriate tables.
• Online table redefinition –  is Oracle recommended method, where package DBMS_REDEFINITION is used.

However I do not have experiences with this package and it was no problem to bring applications down for a while, so I decided to do it manually step by step.

Here are steps I used to migrate tables:

1. Choose which table(s) to migrate –  you can check how much space each table is occupying with this query:

   SELECT table_name, sum(bytes)/1024/1024 size_mb
       FROM (SELECT s.segment_name AS table_name,
                    s.bytes
             FROM user_SEGMENTS s
             WHERE s.segment_type = 'TABLE'
             UNION ALL
             SELECT l.table_name AS table_name,
                    s.bytes
             FROM user_SEGMENTS s,
                  user_lobs l
             WHERE s.segment_name = l.segment_name)
       group by table_name
       order by size_mb desc;

2. Get SQL DDL for your table
3. Create new table, with same structure:

   CREATE TABLE  "NEW_TABLE" 
      (	-- some column definitions  etc.
   	"DATA" BLOB, 
      )
   lob (data) 
   store as securefile
   (compress high
   deduplicate);

   Basically this is usual CREATE TABLE command, but you have to add lob definition to the end.   For SecureFiles you must explicitly enable compression (with appropriate compression level –   I used high, because attached files are accessed rarely, so space was main concern).   Another nice feature is deduplication, which you can enable too. Deduplication basically assures that each file is stored only once, even if uploaded many times by different users ( for each LOB a hash of data is calculated,   If another LOB is inserted with same hash,  it is not stored rather it only references the existing one).

4. Insert data into new table:

   INSERT INTO NEW_TABLE SELECT * FROM OLD_TABLE;

5. Drop old table :

   DROP TABLE OLD_TABLE;

6. Rename new table to original name:

   RENAME NEW_TABLE to OLD_TABLE;

7. Recreate any constrains, triggers, indexes etc.
8. Now you can check again space occupied by tables and see how much space you spared – use script from step 1.  Or you can check free space in tablespace(s) by this script:

   select tablespace_name, sum(bytes)/1024/1024 "Free_MB" from user_free_space
   group by tablespace_name