13.4 Guidelines for Creating Terabyte sized LOBs

To create terabyte LOBs in supported environments, use the following guidelines to make use of all available space in the tablespace for LOB storage.

• Single Data File Size Restrictions:

  There are restrictions on the size of a single data file for each operating system. Hence, add more data files to the tablespace when the LOB grows larger than the maximum allowed file size of the operating system on which your Oracle Database runs.

• 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 metadata for the extent. If the number of extents is large, then 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.

• Creating a Tablespace and Table to Store Terabyte LOBs
  The following example illustrates how to create a tablespace and table to store terabyte LOBs.

13.4.1 Creating a Tablespace and Table to Store Terabyte LOBs

The following example illustrates how to create a tablespace and table to store terabyte LOBs.

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 print_media_backup
  (product_id NUMBER(6), 
   ad_id NUMBER(6), 
   ad_composite BLOB, 
   ad_sourcetext CLOB, 
   ad_finaltext CLOB, 
   ad_fltextn NCLOB, 
   ad_textdocs_ntab textdoc_tab, 
   ad_photo BLOB, 
   ad_graphic BLOB, 
   ad_header adheader_typ)
   NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab5 
   LOB(ad_sourcetext) STORE AS (TABLESPACE lobtbs1 CHUNK 32768 PCTVERSION 0 
                                NOCACHE NOLOGGING
                                STORAGE(INITIAL 1000M NEXT 1000M MAXEXTENTS 
                                UNLIMITED));