使用如下语法：ALTER TABLE MOVE TABLESPACE LOB () STORE AS (TABLESPACE );
其中STORE AS 修改LOB segment段名字的方法,我在817.3的测试中没有效果;而且还导致表也被Moved(注重测试中ROWID的改变).
当分区包括LOB时候，语法稍有不同；
alter table move partition lob () store as ( tablespace )
具体参考http://asktom.Oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:378418239571
SQL> CREATE TABLE loBTab (
recid NUMBER(5),
lobcol CLOB )
LOB (lobcol) STORE AS (TABLESPACE USERS)
TABLESPACE USERS; 2 3 4 5
Table created.
SQL> desc lobtab
Name Null? Type
----------------------------------------- -------- ----------
RECID NUMBER(5)
LOBCOL CLOB
SQL> select index_name, tablespace_name,index_type from user_indexes where table_name = 'LOBTAB';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
INDEX_TYPE
---------------------------
SYS_IL0000119374C00002$$ USERS
LOB
LOB Index必须和LOB Segment在一个表空间中。察看有用的*_lobs视图
SQL> select table_name,column_name,segment_name,index_name from user_lobs;
LOBTAB LOBCOL
SYS_LOB0000119374C00002$$ SYS_IL0000119374C00002$$
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_name,index_type from user_indexes where table_name = 'LOBTAB';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
INDEX_TYPE
---------------------------
SYS_IL0000119374C00002$$ 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_name,column_name,segment_name,index_name from user_lobs;
LOBTAB LOBCOL
SYS_LOB0000119374C00002$$ SYS_IL0000119374C00002$$
|
