移動(dòng)LOB類型數(shù)據(jù)段
2024-07-21 02:34:18
供稿：網(wǎng)友
使用如下語法：ALTER TABLE MOVE TABLESPACE LOB () STORE AS (TABLESPACE ); 其中STORE AS 修改LOB segment段名字的方法,我在817.3的測試中沒有效果;而且還導(dǎo)致表也被Moved(注重測試中ROWID的改變). 當(dāng)分區(qū)包括LOB時(shí)候，語法稍有不同； alter table move partition lob () store as ( tablespace ) 具體參考http://asktom.Oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:378418239571SQL> CREATE TABLE loBTab (
recid NUMBER(5),
lobcol CLOB )
LOB (lobcol) STORE AS (TABLESPACE USERS)
TABLESPACE USERS; 2 3 4 5Table created.SQL> desc lobtab
Name Null? Type
----------------------------------------- -------- ----------
RECID NUMBER(5)
LOBCOL CLOBSQL> select index_name, tablespace_name,index_type from user_indexes where table_name = 'LOBTAB';INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
INDEX_TYPE
---------------------------
SYS_IL0000119374C00002$$ USERS
LOBLOB Index必須和LOB Segment在一個(gè)表空間中。察看有用的*_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 《---目前表紀(jì)錄的rowid 移動(dòng)LOB SegmentSQL> ALTER TABLE lobtab MOVE LOB (lobcol) STORE AS lobseg (TABLESPACE TOOLS);Table altered.SQL> select rowid from lobtab;ROWID
------------------
AAAdKeACjAAAABqAAA <---rowid改變；雖然表的表空間沒有變，但表確實(shí)也被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 沒有發(fā)現(xiàn)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 USERSSQL> select table_name,column_name,segment_name,index_name from user_lobs;LOBTAB LOBCOL
SYS_LOB0000119374C00002$$ SYS_IL0000119374C00002$$
