熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

移動LOB類型數據段

2013-11-13 16:18:25  來源: Oracle 

  使用如下語法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$$


From:http://tw.wingwit.com/Article/program/Oracle/201311/18121.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.