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