24 | 06 | 2017
Latest Articles
Popular Articles

Administration

Lob Segment Administration

User Rating:  / 1
PoorBest 

Lob Segment Administration

 

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.

 

Adjust lob chunk size

 

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.


Change from CACHE / NOCACHE / CACHE READS

 

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.



Rename lob segment

 

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

 

 

Moving lob to another tablespace and rename lob segment

 

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

 

 

Moving lob partitions to another tablespace

 

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

 

Moving lob partitions to another tablespace and rename lob partition

 

SQL> alter table table_t10 move partition T10_P02 lob(T10_SOMEFIELD2) store as T10_LOBDATA_P02 (tablespace coconut_data);