移动含LOB字段的表到其他表空间

由于表空间紧张问题，经常会有移动表到其他表空间的情况，针对含有lob字段的表，需要单独处理：
先移动表，再移动索引，最后再移动lob字段：
[code]alter table MOVE_TBS_TEST move tablespace YY_DATA;

alter index IND_MOVE_TBS_TEST rebuild tablespace YY_DATA;

alter table MOVE_TBS_TEST move lob(info) store as(tablespace YY_DATA);[/code]
完整过程如下：
[code]SQL> create table move_tbs_test (id number(5),name varchar2(20),info clob) tablespace ts_user;

Table created.

SQL> create index ind_move_tbs_test on move_tbs_test(id) tablespace ts_user;

Index created.

SQL> insert into move_tbs_test values(1001,'Steven',rpad('a',3000,'b'));

1 row created.

SQL> commit;

Commit complete.


SQL> col SEGMENT_NAME for a20
SQL> r
1* SELECT segment_name,segment_type,tablespace_name FROM DBA_SEGMENTS where segment_name in('MOVE_TBS_TEST','IND_MOVE_TBS_TEST','SYS_LOB0001087526C00003$$','SYS_IL0001087526C00003$$')

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ ------------------------------
IND_MOVE_TBS_TEST INDEX TS_USER
MOVE_TBS_TEST TABLE TS_USER
SYS_LOB0001087526C00 LOBSEGMENT TS_USER
003$$

SYS_IL0001087526C000 LOBINDEX TS_USER
03$$


SQL> alter table MOVE_TBS_TEST move tablespace YY_DATA;

Table altered.

SQL> SELECT segment_name,segment_type,tablespace_name FROM DBA_SEGMENTS where segment_name in('MOVE_TBS_TEST','IND_MOVE_TBS_TEST','SYS_LOB0001087526C00003$$','SYS_IL0001087526C00003$$')
2 ;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ ------------------------------
IND_MOVE_TBS_TEST INDEX TS_USER
MOVE_TBS_TEST TABLE YY_DATA
SYS_LOB0001087526C00 LOBSEGMENT TS_USER
003$$

SYS_IL0001087526C000 LOBINDEX TS_USER
03$$


SQL> alter index IND_MOVE_TBS_TEST rebuild tablespace YY_DATA;

Index altered.

SQL> SELECT segment_name,segment_type,tablespace_name FROM DBA_SEGMENTS where segment_name in('MOVE_TBS_TEST','IND_MOVE_TBS_TEST','SYS_LOB0001087526C00003$$','SYS_IL0001087526C00003$$');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ ------------------------------
IND_MOVE_TBS_TEST INDEX YY_DATA
MOVE_TBS_TEST TABLE YY_DATA
SYS_LOB0001087526C00 LOBSEGMENT TS_USER
003$$

SYS_IL0001087526C000 LOBINDEX TS_USER
03$$


SQL> alter table MOVE_TBS_TEST move lob(info) store as(tablespace YY_DATA);

Table altered.

SQL> SELECT segment_name,segment_type,tablespace_name FROM DBA_SEGMENTS where segment_name in('MOVE_TBS_TEST','IND_MOVE_TBS_TEST','SYS_LOB0001087526C00003$$','SYS_IL0001087526C00003$$');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ ------------------------------
IND_MOVE_TBS_TEST INDEX YY_DATA
MOVE_TBS_TEST TABLE YY_DATA
SYS_LOB0001087526C00 LOBSEGMENT YY_DATA
003$$

SYS_IL0001087526C000 LOBINDEX YY_DATA
03$$


SQL>
SQL> drop table MOVE_TBS_TEST purge;

Table dropped.

SQL> SELECT segment_name,segment_type,tablespace_name FROM DBA_SEGMENTS where segment_name in('MOVE_TBS_TEST','IND_MOVE_TBS_TEST','SYS_LOB0001087526C00003$$','SYS_IL0001087526C00003$$');

no rows selected

SQL>[/code]