What exactly is a LOB? LOB is the acronym for Large OBject. A table can have a column of the type LOB, this column holds unstructured data like documents, images, spreadsheets, xml files … When you create a LOB column for a table, you are actually creating a separate LOB segment. And when creating an LOB segment, also a LOB index is created together with it, that helps to find the data of the LOB, which is stored in different places in a tablespace.
This is how the LOB segments look like:
SYS_LOBXXXXXXXXXXCXXXXX$$, where XXXX is a hexadecimal number
And the index name of the LOB segement:
SYS_ILXXXXXXXXXXXCXXXXX$$, where XXXX is a hexadecimal number.
The hexadecimal numbers for both the LOB segment and the LOB index are the same.
There are several types of LOBs that can be found in the Oracle database:
BLOB: Binary LOBs
CLOB: Character LOBs
NCLOB: National Character LOBs
BFILE: Binary File
But in ABAP dictionary the datatype represented by LOBs are:
RATRING BLOB
STRING CLOB
LOBs can be stored as basicfiles or securefiles. Basicfiles are the old way of storing these segments, the recommended way of storing LOB segments is now securefile. The main reasons for this would be performance, additional features and security. So it is really recommended in case you still have LOB segments stored as basicfiles, to convert them into securefiles. This can be done easily, as a reorganization option via brtools.
It is possible to check the LOB segments that are not stored as secure LOBs via the command:
SELECT DISTINCT TC.TABLE_NAME, TE.SECUREFILE FROM DBA_TAB_COLUMNS TC, DBA_TABLES T, DBA_LOBS TE WHERE TC.TABLE_NAME = T.TABLE_NAME and TC.TABLE_NAME = TE.TABLE_NAME and TE.TABLE_NAME = T.TABLE_NAME and TC.OWNER LIKE 'SAP%' and (TC.DATA_TYPE = 'BLOB' OR TC.DATA_TYPE = 'CLOB') and SECUREFILE = 'NO';
These segments can be reorganized as secure LOBs with brtools via this path:
BRTOOLS => Segment management -> Reorganize tables -> choose reorganization option “lob2lob”
In Oracle 11 and 12 there is also a parameter name db_securefile that specifies how should a LOB segment be created by default.
These are the possible values according to Oracle documentation:
” NEVER
Any LOBs that are specified as SecureFiles are created as BasicFiles LOBs. All SecureFiles-specific storage options and features (for example, compress, encrypt, deduplicate) will cause an exception. The BasicFiles LOB defaults will be used for storage options not specified.
PERMITTED
LOBs are allowed to be created as SecureFiles.
PREFERRED
All LOBs are created as SecureFiles unless BASICFILE is explicitly specified in the LOB storage clause or the tablespace is a Manual Segment Space Management tablespace. When PREFERRED is set, cases where BASICFILE would otherwise be inherited from the partition or column level LOB storage are ignored; the LOBs will be created as SecureFiles instead.
ALWAYS
Attempts to create all LOBs as SecureFiles LOBs but creates any LOBs not in an Automatic Segment Space Managed (ASSM) tablespace as BasicFiles LOBs, unless SECUREFILE is explicitly specified. Any BasicFiles LOB storage options that are specified will be ignored and the SecureFiles LOB defaults will be used for all storage options not specified.
IGNORE
The SECUREFILE keyword and all SecureFiles options are ignored “
But SAP mentions in the note 1426979 for Oracle 11g only these options: permitted, never, always and ignore and clearly states that for SAP no other value is allowed, except “permitted”, that is also the default value. This means that a new created LOB, that has no specific options added under Oracle 11g, will be created by default as basicfile and this can be converted to securefile anytime.
But what about Oracle 12c? The problem is that SAP has not updated this information for 12c, but from what I know, is that the new default for 12c is the value PREFFERED which means that LOBs are now created as securefiles directly, if there is nothing else specifically mentioned in the creation clause and it is also the only accepted value by SAP for Oracle 12c.
From my experience I had a lot of situations when LOB segments increased very large and had to be reorganized. I recommend that these are regularly checked and reorganized when feasible. Here are some commands that will for sure help during the process. And remember, do not panic if after the reorganization you will not find that LOB segment in the database, this is recreated during the reorganization with another name.
How to check out the LOBs from a table and the mapped columns:
select SEGMENT_NAME,owner,table_name,column_name from dba_lobs where TABLE_NAME='table_name';
Vice versa you can also check what table belongs to a lob segment:
select owner, table_name, column_name FROM dba_lobs WHERE segment_name="lob_segment";
How to check the size of the LOB:
select sum(bytes)/1024/1024 from dba_extents where segment_name="XXXXXXX";
How to get an approximation of how much really does a LOB segment occupy and check if it fragmented:
SELECT 'table_name' TABLE_NAME, 'column_name' COLUMN_NAME, ROUND(SUM(DBMS_LOB.GETLENGTH("column name"))/ 1024 / 1024) "NET (MB)"
FROM "object_owner"."table_name";
Important SAP Notes about this topic:
563359 – Performance optimization for tables with LOB columns
2384534 – LOB conversion and table compression with BRSPACE 7.40 for Oracle 12c
2142497 – What to do for large lobsegment on Oracle database?
500340 – FAQ: LOBS
1426979 – Oracle 11g: SecureFiles – The new way to store LOB data