LOB CLOB BLOB, undo and PCTVERSION Oracle Database Tips by Donald Burleson

Oracle handles large objects (LOB) different than text-based table data.  In a traditional table, Oracle stores the data for in-flight transactions (previous values) in the UNDO segment, so that the transaction can be rolled-back in case of failure.  A shortage of UNDO causes the ORA-01555 error, but with BLOB storage we see the error "ORA-22924 snapshot too old".

Using the PCTVERSION parameter

With tables containing LOB's (CLOB, BLOB), we see a different mechanism whereby the previous image is stored within the data block itself (to ensure read consistency), and the PCTVERSION parameter governs this mechanism.

The default value for PCTVERSION is 10, meaning that 10% of the LOB storage space is reserved for UNDO.  The docs note:

The Oracle "LOB" storage clause lets you specify the LOB tablespace, storage in row, CHUNK and PCTVERSION settings as well as CACHE status, and if NOCACHE is specified whether the LOB storage should be LOGGING or NOLOGGING.  For example:

An example creation of a table using a BLOB datatype is shown in Listing 1. It just as easily could have been a CLOB.

create table internal_graphics (

  graphic_id number,

    graphic_desc varchar2(30),

    graphic_blob blob,

    graphic_type VARCHAR2(4))

    lob (graphic_blob) store as glob_store (

    tablespace raw_data

    storage (initial 100k next 100k pctincrease 0)

    chunk 4

    pctversion 10

   INDEX glob_index (

   tablespace raw_index))

   TABLESPACE appl_data

  storage (initial 1m next 1m pctincrease 0);

 

You can also specify PCTVERSION in a TYPE definition:

 

SQL> CREATE OR REPLACE TYPE clob_demo (

  2  clob_id       NUMBER,

  3  clob_value CLOB

  4 );

 

SQL> CREATE TABLE clob_table OF clob_demo

  5  LOB (clob_value) STORE AS clob_store (

  6  TABLESPACE raw_data

  7  STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)

  8  CHUNK 4

  9  PCTVERSION 10

 10  INDEX clob_index (

 11  TABLESPACE raw_index))

 12  TABLESPACE appl_data

 12* STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)

 

If you like Oracle tuning, see my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.