How to Create Gigabyte LOBs

LOBs in Oracle8i 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 Suitable Value or UNLIMITED: MAXEXTENTS in the LOB storage clause should be set to a reasonable value to suit the projected size of the LOB, or set it to UNLIMITED for safety.

• Use a Large Extent Size: For every new extent created, Oracle8i 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: 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 8, "Sample Application", 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); 
CREATE TABLESPACE lobtbs1 add datafile '/your/own/data/directory/lobtbs_2.dat' 
size 2000M reuse; 
ALTER TABLESPACE lobtbs1 add datafile '/your/own/data/directory/lobtbs_2.dat'  
size 1000M 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));