See also Lob Segments
useful views are user_lobs and user_lob_partitions
It is possible to alter online some lob attributes whereas some other cannot be altered dynamically.
SQL> select cache,pctversion from user_lobs where table_name='TABLE_T9';
CACHE PCTVERSION
NO
SQL> alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (nocache logging pctversion 10);
Table altered.
SQL> select cache,pctversion from user_lobs where table_name='TABLE_T9';
CACHE PCTVERSION
NO 10
This is the error message we get when we try to use 2 attributes which are mutually exclusive. In this example we use the CACHE and the LOGGING attribute. Once we adjust our lob segment to have the cache attribute/behaviour the logging attribute becomes obsolete. (since any lob data having the cache attribute always has the logging attribute)
SQL> alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (cache logging pctversion 10);
alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (cache logging pctversion 10)
*
ERROR at line 1:
ORA-22853: invalid LOB storage option specification
Here is the right syntax
SQL> alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (cache pctversion 10);
Table altered.
This is the error message we get when we try to adjust the lob chunck size to a value > 32K
SQL> alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (chunk 128K);
alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (chunk 128K)
*
ERROR at line 1:
ORA-22851: invalid CHUNK LOB storage option value
Here is the right syntax ( using the max chunck value of 32K )
SQL> alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (chunk 32K);
Table altered.
SQL> alter table table_t9 modify lob (T9_SOMEFIELD2) (cache reads logging);
Table altered.
SQL> alter table table_t9 modify lob (T9_SOMEFIELD2) (cache reads nologging);
Table altered.
SQL> select segment_name,tablespace_name from user_lobs where table_name='TABLE_T9';
SEGMENT_NAME TABLESPACE_NAME
T9_LOB COCONUT_LOB
SQL> alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data;
Table altered.
SQL> select segment_name,tablespace_name from user_lobs where table_name='TABLE_T9';
SEGMENT_NAME TABLESPACE_NAME
T9_LOB_DATA COCONUT_LOB
SQL> alter table table_t9 move lob(T9_SOMEFIELD2) store as T9_lob_data (tablespace coconut_data);
Table altered.
SQL> select segment_name,tablespace_name from user_lobs where table_name='TABLE_T9';
SEGMENT_NAME TABLESPACE_NAME
T9_LOB_DATA COCONUT_DATA
SQL> select partition_name,tablespace_name,lob_name from user_lob_partitions where table_name='TABLE_T10';
PARTITION_NAME TABLESPACE_NAME
LOB_NAME
T10_P01 COCONUT_DATA_01
T10_LOB
T10_P02 COCONUT_DATA_02
T10_LOB
SQL> alter table table_t10 move partition T10_P01 lob(T10_SOMEFIELD2) store as (tablespace coconut_data);
SQL> select partition_name,tablespace_name,lob_name from user_lob_partitions where table_name='TABLE_T10';
PARTITION_NAME TABLESPACE_NAME
LOB_NAME
T10_P01 COCONUT_DATA
T10_LOB
T10_P02 COCONUT_DATA_02
T10_LOB
SQL> alter table table_t10 move partition T10_P02 lob(T10_SOMEFIELD2) store as T10_LOBDATA_P02 (tablespace coconut_data);