Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)

Part Number A88879-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Modeling and Design, 5 of 21


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:

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.

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... 


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback