移动普通表时语法:
ALTER TABLE table_name move [tablespace tablespace_name] LOB (lob_item) STORE AS [lob_segment]
(
TABLESPACE tablespace_name
(STORAGE.....)
ENABLE|DISABLE STORAGE IN ROW
CHUNK integer
PCTVERSION integer
RETENTION
FREEPOOLS integer
CACHE|NOCACHE|CACHE READS
INDEX lobindexname
);
移动分区表时语法:
alter table move partition lob () store as ( tablespace );
SQL> create table tclob(
2 id number,
3 name clob
4 )tablespace users
5 lob(name) store as tc_name(tablespace users );
表已创建。
已用时间: 00: 00: 00.04
SQL> select segment_name, segment_type, tablespace_name
2 from user_segments
3 where segment_type like 'LOB%'
4 or segment_name = 'TCLOB';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE
------------------------- ------------------------------ ----------
SYS_IL0000011838C00002$$ LOBINDEX USERS
TC_NAME LOBSEGMENT USERS
TCLOB TABLE USERS
已用时间: 00: 00: 00.01
SQL> select table_name, column_name, segment_name, tablespAce_name, index_name
2 from user_lobs
3 where table_name ='TCLOB';
TABLE_NAME COLUMN_NAM SEGMENT_NAME TABLESPACE INDEX_NAME
---------- ---------- --------------- ---------- ------------------------------
TCLOB NAME TC_NAME USERS SYS_IL0000011838C00002$$
已用时间: 00: 00: 00.00
SQL> select object_name, object_id, data_object_id
2 from user_objects
3 where object_name = 'TCLOB';
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
TCLOB 11838 11838
已用时间: 00: 00: 00.00
SQL> insert into tclob(id,name)values(1,'a') ;
已创建 1 行。
已用时间: 00: 00: 00.00
SQL> commit;
提交完成。
已用时间: 00: 00: 00.00
SQL> select rowid from tclob;
ROWID
------------------
AAAC4+AAEAAAAGjAAA
已用时间: 00: 00: 00.00
SQL> alter table tclob move lob(name) store as (tablespace rman);
表已更改。
已用时间: 00: 00: 00.07
SQL> select segment_name, segment_type, tablespace_name
2 from user_segments
3 where segment_type like 'LOB%'
4 or segment_name = 'TCLOB';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE
------------------------- ------------------------------ ----------
TCLOB TABLE USERS
SYS_IL0000011838C00002$$ LOBINDEX RMAN
TC_NAME LOBSEGMENT RMAN
已用时间: 00: 00: 00.03
SQL> select table_name, column_name, segment_name, tablespAce_name, index_name
2 from user_lobs
3 where table_name ='TCLOB';
TABLE_NAME COLUMN_NAM SEGMENT_NA TABLESPACE INDEX_NAME
---------- ---------- ---------- ---------- ------------------------------
TCLOB NAME TC_NAME RMAN SYS_IL0000011838C00002$$
SQL> select object_name, object_id, data_object_id
2 from user_objects
3 where object_name = 'TCLOB';
OBJECT_NAM OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
TCLOB 11838 11841
已用时间: 00: 00: 00.00
SQL> select rowid from tclob;
ROWID
------------------
AAAC5BAAEAAAAQNAAA
已用时间: 00: 00: 00.01
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11134237/viewspace-631299/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11134237/viewspace-631299/