SLOUG October 11, 2001

1
SLOUG - October 11, 2001

• LOBs
• Lisa Reinheimer - Technology Partners Inc.
  lisa_reinheimer_at_hotmail.com

2
Oracle Large Objects - LOBs

• What are LOBs?
• Different Types of LOBs
• Why they are better than the old stuff
• What DBAs must know
• How to convert existing data

3
What are LOBs anyway ?

• a LOB is not just -
• a strategic shot in a game of tennis
• another wedge for your short game - dont forget
  the 14 club limit - USGA rule 4-4.a!
• an astropunk jazz band
• a LOB is
• A new data type intended to replace long and long
  raw data.
• Audio, video, text, graphics, images, etc.

4
Types of LOBS

• External
• Binary File, BFILE
• Internal
• Binary , BLOB
• Character, CLOB
• Multibyte character, NCLOB

5
Advantages of using LOBs

• More than one LOB column per table
• A single LOB can be up to 4 Gig in size
• PL/SQL and sqlplus support
• Piecewise read and write
• Control over storage
• in-line or out-of-line
• partition friendly
• separate tablespace or in a filesystem

6
LOBS - What DBAs Must Know

• Understand the data and its behavior
• longevity, frequency of updates, criticality,
  etc.
• Determine the type of LOB to use
• Setup the physical storage parameters
• tablespace, chunk size, lob index, in-line,
  cache, logging, pctversion
• Redo and Undo issues

7
Example

• create table some_docs
• ( DOC_ID NUMBER NOT NULL
• ,CRC32 VARCHAR2(8) NOT
  NULL
• ,IMAGE BLOB NOT NULL
• ) TABLESPACE STD_TBLSPC PCTFREE
  10 PCTUSED 40
• STORAGE ( INITIAL 1M NEXT
  1M PCTINCREASE 0 )
• LOB (IMAGE)
• STORE AS somedoc_blobseg
• (tablespace BLOB_TBLSPC
• storage (initial 100M NEXT 100M PCTINCREASE 0)
• disable storage in row
• chunk 32768
• pctversion 10
• index somedoc_blobseg_idx
• nocache nologging)

8
Data Conversion Alternatives

• Create table as select
• Export long or long raw / Import clob or blob
• dbms To_Lob Function not available in PL/SQL
• Custom Program