Alter a logical network table's pageblob attribute's LOB segment from NOCACHE to CACHE

Summary

Instructions provided describe how to alter a logical network table's pageblob attribute's LOB segment from NOCACHE to CACHE. If a logical network table containing a pageblob attribute is created without specifying the storage options for the LOB segment, Oracle's default setting is NOCACHE, meaning LOB values are not brought into the buffer cache.

To improve performance it is recommended the LOB segment that represents the pageblob attribute is CACHED, reducing physical I/O operations.

Procedure

1. Identify all the logical network tables where the pageblob attribute's LOB segment is set to NOCACHE.
   
   

   Code:
   SQL> SELECT table_name, cache
      2  FROM user_lobs
      3  WHERE column_name = 'PAGEBLOB'
      4  AND cache = 'NO';
   
   TABLE_NAME              CACHE
   ----------------------- -------
   N_3_JTOPO               NO

   
   
2. Alter the table and verify that the LOB segment is now cached.
   

   Code:
   SQL> ALTER TABLE n_3_jtopo
     2   MODIFY LOB (pageblob)(CACHE);
   
   Table altered.
   
   SQL> SELECT table_name, cache
      2  FROM user_lobs
      3  WHERE column_name = 'PAGEBLOB'
      4  AND table_name = 'N_3_JTOPO';
   
   TABLE_NAME              CACHE
   ----------------------- -------
   N_3_JTOPO               YES

   
   

   Note:
   See Oracle's documentation for additional information.