Oracle Advanced Compression Option for LOB

Good news is that with oracle 12C oracle by default enabled SECUREFILE  for LOB objects. But it is not enabled by default for Oracle internal storage like system table spaces.

Here is a link that will give more information.

http://blog.dbi-services.com/oracle-12c-securefiles-enhancements/

Sample code to convert a column from Basicfile lob to securefile lob or to change any other component.

DECLARE
   v_needschange   NUMBER := 0;
BEGIN
   SELECT COUNT (1)
     INTO v_needschange
     FROM user_lobs
    WHERE     table_name = 'TAB1'
          AND column_name = 'COL1'
          AND NOT (    cache = 'YES'
                   AND securefile = 'YES'
                   AND deduplication = 'NO'
                   AND compression = 'NO');

   IF v_needschange > 0
   THEN
      DBMS_OUTPUT.put_line (
         'Column COL1 of table TAB1 is not defined as either secure file or keep duplicates, or no compress or cache as yes');

      EXECUTE IMMEDIATE
         'ALTER TABLE TAB1 ADD (COL11 BLOB) LOB(COL11) STORE AS SECUREFILE (KEEP_DUPLICATES CACHE)';

      EXECUTE IMMEDIATE
         'UPDATE TAB1 SET COL11 = COL1';

      EXECUTE IMMEDIATE 'ALTER TABLE TAB1 DROP (COL1)';

      EXECUTE IMMEDIATE
         'ALTER TABLE TAB1 RENAME COLUMN COL11 TO COL1';
   END IF;
END;
/