移动LOB类型数据段

使用如下语法ALTER TABLE MOVE TABLESPACE LOB () STORE AS (TABLESPACE );

其中STORE AS 修改LOB segment段名字的方法我在的测试中没有效果;而且还导致表也被Moved(注意测试中ROWID的改变)

当分区包括LOB时候语法稍有不同

alter table move partition lob () store as ( tablespace )

具体参考

SQL> CREATE TABLE lobtab (

recid NUMBER()

lobcol CLOB )

LOB (lobcol) STORE AS (TABLESPACE USERS)

TABLESPACE USERS;

Table created

SQL> desc lobtab

Name Null? Type

RECID NUMBER()

LOBCOL CLOB

SQL> select index_name tablespace_nameindex_type from user_indexes where table_name = LOBTAB;

INDEX_NAME TABLESPACE_NAME

INDEX_TYPE

SYS_ILC$$ USERS

LOB

LOB Index必须和LOB Segment在一个表空间中察看有用的*_lobs视图

SQL> select table_namecolumn_namesegment_nameindex_name from user_lobs;

LOBTAB LOBCOL

SYS_LOBC$$ SYS_ILC$$

SQL> select rowid from lobtab;

ROWID

AAAdJRACiAAAACqAAA 《目前表纪录的rowid

移动LOB Segment

SQL> ALTER TABLE lobtab MOVE LOB (lobcol) STORE AS lobseg (TABLESPACE TOOLS);

Table altered

SQL> select rowid from lobtab;

ROWID

AAAdKeACjAAAABqAAA <rowid改变虽然表的表空间没有变但表确实也被move了

SQL> select index_name tablespace_nameindex_type from user_indexes where table_name = LOBTAB;

INDEX_NAME TABLESPACE_NAME

INDEX_TYPE

SYS_ILC$$ TOOLS

LOB

没有发现LOBSEG看来语法没有起作用

SQL> SELECT segment_name segment_type tablespace_name FROM user_segments where segment_name IN (LOBTAB LOBSEG);

SEGMENT_NAME

SEGMENT_TYPE TABLESPACE_NAME

LOBTAB

TABLE USERS

SQL> select table_namecolumn_namesegment_nameindex_name from user_lobs;

LOBTAB LOBCOL

SYS_LOBC$$ SYS_ILC$$