SecureFile LOBs were added to Oracle 11g to provide better performance over the legacy LOB format of BasicFile LOBs.
New features of SecureFile LOBs:
- Intelligent LOB compression allows user to compress file.
- Intelligent LOB encryption in place and allows for random reads and writes.
- Deduplication option allows for duplicate data to only be stored once.
- LOB data path optimization allows for logical cache above storage level, read prefetching, new caching modes, vectored IO
In Oracle 12c the initialization parameter of DB_SECUREFILE determines use of LOB type with the following options:
ALWAYS – Attempts to use SecureFile LOBS but uses BasicFile LOB if ASSM is not in use.
PERMITTED – Allows SecureFile LOBs to be created.
PREFERRED – All LOBs are SecureFile unless BasicFile is specified. When PREFERRED is sent inherited type from partition and columns are ignored.
NEVER – Disallows use of SecureFile LOBs, attempts to create SecureFile LOBs will be created as BasicFile.
IGNORE – SECUREFILE keyword and all options are ignored.
Online redefinition can be performed at table/partition level and does not require the table or partition to be taken offline. However redefinition will require storage equal to double current LOB and global indexes will have to be rebuilt. Redefined SecureFiles inherit LOB column settings for deduplication, encryption, and compression made at original BasicFile LOB.
DBMS_LOB package has several options for redefine:
DBMS_LOB.GETOPTIONS – give current settings of SecureFile LOB.
DBMS_LOB.SETOPTIONS – allows to change current settings.
DBMS_LOB.IISECUREFILE – Determines if it is SECUREFILE LOB or not.
DBMS_SPECE.SPACE_USAGE – Procedure returns amount of space used in blocks by all LOBs and can only be used if auto segment space management is in use.
Larry Catt
OCP