很多人都知道在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据，另一个用来存放索引，并且它们都会存储在对应表指定的表空间中。但是当我们用alter table tb_name move tablespace tbs_name;来对表做空间迁移时只能移动非lob字段以外的数据，而如果需要同时移动lob相关字段的数据，就必需用如下的含有特殊参数据的语句来完成。
alter table tb_name move tablespace tbs_name
lob (col_lob1,col_lob2) store as(tablesapce tbs_name);
|
下面是一个具体的示例：
connected to oracle8i enterprise edition release 8.1.7.0.0
connected as scott
sql> desc test_blob;
name type nullable default comments
--------- ------------ -------- ------- --------
file_name varchar2(25) y
file_bin blob y
file_bin2 blob y
sql>
sql> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'sys_%'
4 /
segment_name segment_type tablespace_name
------------------------ - ---------------- --------------
sys_il0000025842c00003$$ lobindex system
sys_il0000025842c00002$$ lobindex system
sys_lob0000025842c00002$$ lobsegment system
sys_lob0000025842c00003$$ lobsegment system
6 rows selected
sql> alter table test_blob move tablespace tools;
table altered
sql>
sql> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'sys_%'
4 /
segment_name segment_type tablespace_name
------------------------- ---------------- --------------
sys_il0000025842c00003$$ lobindex system
sys_il0000025842c00002$$ lobindex system
sys_lob0000025842c00002$$ lobsegment system
sys_lob0000025842c00003$$ lobsegment system
6 rows selected
sql> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'test_blob';
segment_name segment_type tablespace_name
------------------------ ------------- -----------------
test_blob table tools
sql> alter table test_blob move tablespace system;
table altered
sql> alter table test_blob move
2 tablespace tools
3 lob (file_bin,file_bin2) store as
4 (tablespace tools);
table altered
sql> select t.segment_name, t.segment_type, t.tablespace_name
2 from sys.user_segments t
3 where t.segment_name like 'sys_%';
segment_name segment_type tablespace_name
--------------------------- ------------------ ------------------------
sys_il0000025842c00003$$ lobindex tools
sys_il0000025842c00002$$ lobindex tools
sys_lob0000025842c00002$$ lobsegment tools
sys_lob0000025842c00003$$ lobsegment tools
6 rows selected
|
闽公网安备 35060202000074号