How to Change the LOB Storage Parameters after Creation

Goal:
Actually the goal behind creating this article is to try to provide a precise specific information about altering the storage parameters of a table that contains one or more LOB columns and provide a life examples on how to do so.
(文章的目的是提供一个精确的具体信息关于修改一个包括一个或者多个LOB字段的表的storage参数的方法)

Solution:
When creating a LOB column (CLOB, NCLOB and BLOB) that automatically creates 2 additional disk segments for this LOB column - a LOBINDEX and a LOBSEGMENT. These can be viewed, along with the LOB attributes, using the dictionary views:
select * from DBA_LOBS
select * from ALL_LOBS
select * from USER_LOBS

After creating the LOB column(LOBSEGMENT and LOBINDEX) you can only change the following Storage Parameters,The other LOB parameters cannot be changes after creation:
TABLESPACE
IN ROW
CHUNK
PCTVERSION
RETENTION
FREEPOOLS
CACHE

Changing the LOB Storage parameters can only be done by using the Alter Table Move command as following:
ALTER TABLE

Move LOB() STORE AS ( )

e.g.
SQL> alter table TEST_LOB_TAB move lob(content) store as (tablespace system);

The above statement will move the whole "content" LOB segment(data and index) to the SYSTEM tablespace.
When creating a table, if you specify a tablespace for the LOB index for a non-partitioned table,then your specification of the tablespace will be ignored and the LOB index will be co-located with the LOB data. Partitioned LOBs do not include the LOB index syntax.(当你为非分区表指定lob index的tbs时,表空间将被忽略,lob index将被和lob data放在同一个表空间内)

And therefore to put the lobindex in a different tablespace you have to use the above mentioned MOVE command to move the whole LOB to another tablespace(所以如何想把lob index放在不同的表空间时，只能通过move命令去移动整个的LOB(index+data)to相应的tbs).

e.g.
SQL> select index_name, index_type, tablespace_name from dba_indexes where table_name like 'TEST_LOB_TAB';
    INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME
    ------------------------------ --------------------------- ------------------------------
    XIE2_TEST_LOB_TAB              LOB ! ;                        USERS

SQL> alter table TEST_LOB_TAB move lob(content) store as (tablespace system);
Table altered.

SQL> select index_name, index_type, tablespace_name from dba_indexes where table_name like 'TEST_LOB_TAB';
     INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME
     ------------------------------ --------------------------- ------------------------------
     XIE2_TEST_LOB_TAB              LOB    ! ;          &! nbsp;&nb sp;         SYSTEM

有关LOB字段的表如何move tablespace，试验部分相关操作，记录如下：

1. 新建用户语句：
create user ecc_view identified by "ecc"
default tablespace data01
temporary tablespace TEMP
profile DEFAULT;

2. Grant/Revoke role privileges
   grant connect to ecc_view ;
   grant resource to ecc_view ;
   grant create synonym, create view, create database link to ecc_view (10g中的connect中没有这个权限，需要单独授权)

3. revoke quota on tablespace except data01
REVOKE UNLIMITED TABLESPACE FROM "ecc_view"
ALTER USER "ecc_view" QUOTA UNLIMITED ON "data01"

   a. move table to tablespace(非lob字段的对象)
      select 'alter table '||segment_name ||' move tablespace data01 ; '
      from user_segments a
      where a.tablespace_name <> 'data01'
      and a.segment_type = 'TABLE'

   b. rebuild index
      select ' alter index '||index_name||' rebuild tablespace data01;'
      from user_ind_columns b, user_segments c
      where b.index_name = c.segment_name
      and c.tablespace_name <> 'data01'

   c. lob segment move tablespace
      select ' alter table '|| table_name || ' move lob('||column_name||') store as (tablespace data01); ' from user_lobs a
      where a.tablespace_name <> 'data01'

   4. check
      select * from user_segments a
      where a.tablespace_name = 'USERS'
      --null

      select * from user_objects b
      where b.status <> 'VALID'
      --null

      select * from user_indexes b
      where b.status <> 'VALID'
      --null

参考文献：

Subject: How to Change the LOB Storage Parameters after Creation
Doc ID: 801338.1 Type: HOWTO
Modified Date : 06-APR-2009 Status: MODERATED