Administration

Lob Segment Administration

User Rating:

/ 1

Details: Parent Category: Articles; Created on Thursday, 18 March 2010 20:20; Last Updated on Wednesday, 18 May 2016 12:48; Published on Thursday, 18 March 2010 20:20; Written by Guy Lambregts; Hits: 10603

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);