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:
- Choose which table(s) to migrate – you can check how much space each table is occupying with this query:
Oracle PL/SQL12345678910111213SELECT table_name, sum(bytes)/1024/1024 size_mbFROM (SELECT s.segment_name AS table_name,s.bytesFROM user_SEGMENTS sWHERE s.segment_type = 'TABLE'UNION ALLSELECT l.table_name AS table_name,s.bytesFROM user_SEGMENTS s,user_lobs lWHERE s.segment_name = l.segment_name)group by table_nameorder by size_mb desc;
- Get SQL DDL for your table
- Create new table, with same structure:
Oracle PL/SQL1234567CREATE TABLE "NEW_TABLE"( -- some column definitions etc."DATA" BLOB,)lob (data)store as securefile(compress high
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).
- Insert data into new table:
Oracle PL/SQL1INSERT INTO NEW_TABLE SELECT * FROM OLD_TABLE;
- Drop old table :
Oracle PL/SQL1DROP TABLE OLD_TABLE;
- Rename new table to original name:
Oracle PL/SQL1RENAME NEW_TABLE to OLD_TABLE;
- Recreate any constrains, triggers, indexes etc.
- 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:
Oracle PL/SQL12select tablespace_name, sum(bytes)/1024/1024 "Free_MB" from user_free_spacegroup by tablespace_name