1. What is a LOB?
Ans: The LOB (Large OBject) datatypes enable you to store large blocks of unstructured data up to four gigabytes in size in a table column.
The data stored in a LOB column can be stored within the table itself or in an extra object outside of the table - a segment of type LOBSEGMENT.
To access the data efficiently a segment of type LOBINDEX is created.
If during table creation the option 'enable storage in row' is used for a lob column the lob column value is stored within the table if it is less than approx 4000
Bytes. If above it is stored in the extra lob segment. 'enable storage in row' is the default used by SAP.
If during table creation the option 'disable storage in row' is used the data is in any case stored in the extra lob segment.
2. Are there different types of LOBs?
Ans: Yes,
BLOB: Binary LOBs
CLOB: Character LOBs
NCLOB: National Character LOBs
BFILE: Binary File
3. When did SAP started to use LOBS?
Ans: Starting with release 6.10 large amount of data can be stored in tablecolumns of a LOB type.
4. Why are LOBs used?
Ans: LOBs have advantages compared with the LONG RAW or LONG datatype formerly used:
a table can have many LOB columns but only one LONG column
partitioning can be used
LOBs can have a size of 4GB - LONG only 2GB
LOB data can be accessed randomly and not only sequentially
5. Which SAP tables use LOBs?
Ans: To find out all tables and their columns using LOBS use this sql statement:
select table_name, column_name from dba_lobs where owner = '';
For example the tables DYNPSOURCE, DYNPLOAD, REPOSRC, REPOLOAD are using LOBs.
6. How can I display LOB information?
Ans: LOBs can have a nonabstract name but usually have a name like 'SYS_LOB0000003619C00002$$' in SAP environment
If you want to know to which table and which column a LOB object belongs or vice versa you can query dba_lobs:
dba_lobs: table_name, column_name, segment_name (=lobsegment name), index_name(=lobindex name)
If you want to find out more about the storage parameters of a LOBSEGMENT or LOBINDEX query dba_segments.
7. Which ABAP dictionary datatypes are represented by LOBs on DB?
Ans: dictionary | database
-------------------------
RAWSTRING | BLOB
STRING | CLOB
8. How is an example table with a LOB created on database level?
Ans: create table lobtable (first_column varchar2(10), lob_column BLOB)
tablespace psapuser1d
storage (initial 100k next 100k pctincrease 0 maxextents 300)
lob (lob_column) store as (
enable storage in row
tablespace psapuser1d
storage (initial 3M next 3M pctincrease 0 maxextents 300));
9. How is the storage clause of a LOBSEGMENT or LOBINDEX set and changed?
Ans: LOBSEGMENT
create alter
initial as specified ---
next as specified as specified 2)
maxextents as specified as specified
pctincrease as specified as specified 1)
tablespace as specified ---
LOBINDEX
create alter
initial as lob segment ---
next default initial of TS of lob segm. see create 2)
maxextents unlimited unlimited
pctincrease as lob segment via lob segment 1)
tablespace as lob segment ---
'as specified' means as specified in the create/alter statement for the table
1) alters pctincrease from lobindex to the same value
2) alters nextextent size of lobindex also to the default initial of TS of lob segm.
To modify the lobsegments storage parameter use:
alter table modify lob () (storage (...));
Keep in mind that for locally managed tablespaces you don´t have to adapt the storage clause.
10. What should I consider when I get an ora-1658 during creation of a table containing LOB columns?
Ans: On dictionary managed tablespaces:
If for the LOBSEGMENT or the LOBINDEX no storage clause is specified during creation time of the object containing these LOB objects, the tablespace defaults may be choosen. If the tablespace defaults are set too high consequently an ora-1658 may be fired. Change the tablespace defaults (initial_extent, next_extent) to a lower value then.
You can monitor the tablespace defaults with:
select tablespace_name, initial_extent, next_extent from dba_tablespaces;
You can change the tablespace defaults with:
alter tablespace default storage (initial next );
On locally managed tablespaces changing defaults is not necessary. If you get the error anyway you have to less space available. Add or resize a datafile.
11. Can BR*Tools handle LOBs?
Ans: Yes. All current releases of BR*Tools are able to handle lobs.
12. How is a LOB exported?
Ans: Implicitly together with the table it belongs to. Only conventional path export - not direct path export - is possible. If you try direct path export EXP-00067 occurs.
13. Is LOB data cached in the buffer cache?
Ans: If the data of a lob column is cached or not depends on how the value CACHE is set in DBA_LOBS for this column. If it is set to 'NO', direct reads bypassing the buffer cache are performed. This is the default. Enabling this option in general for all tables having LOB columns can lead to worse response times because of buffer displacement of other data.
Caching can be switched on/off with:
alter table
modify lob CACHE/NOCACHE;
893441 can be implemented, so that also the SAP Data Dictionary supports the creation of cached LOBs.
14. What are the differences between LOBs and other segments regarding undo handling?
Ans: If the data stored in a lobsegment is changed, the before images are NOT stored in undo segments but in the lobsegment itself. In addition during an update the new data does not overwrite the old data but is placed in other blocks to keep the before image where it is. This is done to avoid overhead by copying a large amount of data.
After the changes are commited the before images are kept in the lobsegment depending on the storage parameter PCTVERSION. PCTVERSION can be set individually for each lobsegment and indicates how much of the already occupied space of the lobsegment should be kept to store before images. The following values for PCTVERSION mean:
0: do not reserve any space in the lobsegment for read consistency
10: default; reserve 10% of the space for committed before images
100: reserve all space currently allocated for commited before images
Consequences of this storage behavior are:
an update that shrinks only values in a lob column needs to extend the corresponding lobsegment because the new value in the lob column does not overwrite the old value but is placed in the lobsegment additionally. Possible errors:
ORA-01693: max # extents ... reached in lob segment ...
ORA-01691: unable to extend lob segment ... by ... in tablespace ...
Those errors can be handled in the normal way by increasing the maxextents/nextextentsize respectively the tablespace size.
Other changes to a lobsegment may fail although there is theoretical space available occupied by committed before images
A lobsegment is in worst case factor 100/(100-) larger than neccessary to store the current data
A large PCTVERSION reduces the likelihood of an ora-1555 but leads to larger lobsegment. For PCTVERSION=100 space allocated by commited before images is never freed.
If a read cannot be executed consistent the following errors are fired:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
The ora-01555 neither contain a number for a rollbacksegment nor a rollbacksegment name.
Optimize the select running into the ora-01555. If this is already optimized increase the PCTVERSION with alter table
modify lob () (pctversion );
Further information regarding undo management can be found in Oracle Metalink document 162345.1
15. Where do temporary LOBs come from?
Ans: Under some circumstances it can happen that PSAPTEMP is filled with a significant amount of temporary LOBs. This can be verified with the V$TEMPORARY_LOBS view.
These temporary LOBs are created in SAP J2EE due to technical reasons (guarantee of atomicity of LOB creation) whenever a new LOB is created. In situations with a high LOB creation rate (e.g. when tables with LOBs are imported in the database) this behaviour can be responsible for a significant filling level or an overflow of PSAPTEMP.
16. What are known problems regarding LOBs?
Ans: LOB access may be less performant than long raw access. This should be considered when thinking about conversion of a long raw column to a LOB column (835552)
Segment shrinking on Objects with LOB columns can lead to corruptions on release 10.2.0.2. (1021454). Do not use segment shrinking on this release without having the bugfix of 1021454 applied.
Korruptions on LOBs can be indicated by ORA-22924.
Recovery of changes to a LOB can lead to corruptions
983230: Oracle Release 9.2.0.n (n <= 7) or 10.2.0.2
567866: Oracle Release 8.1.7.4 or 9.2.0.n (n <=2)
Korruptions on LOBs can be indicated by ORA-22924.
17. Where can I get further information about LOBs?
Ans: Further information about LOBS can be found in the Oracle documentation.