How to Create Gigabyte LOBs

LOBs in Oracle8i and higher can be up to 4 gigabytes. To create gigabyte LOBs, use the following guidelines to make use of all available space in the tablespace for LOB storage:

• Single Datafile Size Restrictions: There are restrictions on the size of a single datafile for each operating system (OS). For example, Solaris 2.5 only allows OS files of up to 2 gigabytes. Hence, add more datafiles to the tablespace when the LOB grows larger than the maximum allowed file size of the OS on which your Oracle database runs.

• Set PCT INCREASE Parameter to Zero: PCTINCREASE parameter in the LOB storage clause specifies the percent growth of the new extent size. When a LOB is being filled up piece by piece in a tablespace, numerous new extents get created in the process. If the extent sizes keep increasing by the default value of 50 percent every time, extents will become unmanageably big and eventually will waste unnecessary space in the tablespace. Therefore, the PCTINCREASE parameter should be set to zero or a small value.

• Set MAXEXTENTS to a Suitable Value or UNLIMITED: The MAXEXTENTS parameter limits the number of extents allowed for the LOB column. A large number of extents are created incrementally as the LOB size grows. Therefore, the parameter should be set to a value that is large enough to hold all the LOBs for the column. Alternatively, you could set it to UNLIMITED.

• Use a Large Extent Size: For every new extent created, Oracle generates undo information for the header and other meta data for the extent. If the number of extents is large, the rollback segment can be saturated. To get around this, choose a large extent size, say 100 megabytes, to reduce the frequency of extent creation, or commit the transaction more often to reuse the space in the rollback segment.

Example 1: Creating a Tablespace and Table to Store Gigabyte LOBs

A working example of creating a tablespace and a table that can store gigabyte LOBs follows. The case applies to the multimedia application example in Chapter 10, "Internal Persistent LOBs", if the video Frame in the multimedia table is expected to be huge in size, i.e., gigabytes.

CREATE TABLESPACE lobtbs1 datafile '/your/own/data/directory/lobtbs_1.dat' size 
2000M reuse online nologging default storage (maxextents unlimited); 
ALTER TABLESPACE lobtbs1 add datafile '/your/own/data/directory/lobtbs_2.dat'  
size 2000M reuse;
 
CREATE TABLE Multimedia_tab (  
   Clip_ID         NUMBER NOT NULL,  
   Story           CLOB default EMPTY_CLOB(),  
   FLSub           NCLOB default EMPTY_CLOB(),  
   Photo           BFILE default NULL,  
   Frame           BLOB default EMPTY_BLOB(),  
   Sound           BLOB default EMPTY_BLOB(),  
   Voiced_ref      REF Voiced_typ, 
   InSeg_ntab      InSeg_tab,  
   Music           BFILE default NULL,  
   Map_obj         Map_typ,  
   Comments        LONG
 )  
 NESTED TABLE    InSeg_ntab STORE AS InSeg_nestedtab 
 LOB(Frame) store as (tablespace lobtbs1 chunk 32768 pctversion 0 NOCACHE 
NOLOGGING 
 storage(initial 100M next 100M maxextents unlimited pctincrease 0)); 

Example 2: Creating a Tablespace and Table to Store Gigabyte LOBs

The difference between Example 1 and this example is that one specifies the storage clause during CREATE TABLE and one does it in CREATE TABLESPACE.

• For temporary LOBs, the STORAGE clause has to be specified when creating the temp tablespace

• For persistent LOBs, the STORAGE clause can be specified either when creating tablespace or when creating table

How this Affects the Temporary LOB COPY or APPEND?

The critical factor is setting the PCTINCREASE parameter to 0. Otherwise, the default value is 50%. When a 4gigabyte LOB is being filled up, the extents size expands gradually until it blows up the tablespace, as follows:

1st extent: 100M, 2nd 100M, 3rd, 150M, 4th 225M...