move table,index

1.普通table

alter table p_person move tablespace users;

相关索引

alter table index pk_p_person rebuild online tablespace index;

如果是标准版：

alter table index pk_p_person rebuild  tablespace index;

2.含blob、clob字段

 --移动BLOB、CLOB的字段到指定表空间
alter table Tbl_Name move lob(BLOB1,BLOB2,BLOB3,......) store as (tablespace new_tbs_nm);

     
--移动BLOB、CLOB的字段语法
alter table table_name move [tablespace tbs_name] lob(lob_field1,lob_field2) store as (tablespacenew_tbs_nm);

-如果LOB字段在分区表中，则增加partition关键字，如
alter table table_name move [partition partname] [tablespace tbs_name] lob(field) store as (tablespace new_tbs_nm)

alter table my_lob move lob(pic) store as pic (tablespace indx index pic_lob(tablespace indx));

-------产生批量更改语句-------------------------

--移动表
select distinct 'alter table THAMS.'|| segment_name || ' move tablespace NEWSPACE;' from dba_extents where segment_type='TABLE'；

--移动索引
select distinct 'alter index THAMS.'|| segment_name || ' rebuild tablespace NEWSPACE;' from dba_extents where segment_type='INDEX' ;
       
--如果有分区表则需要移动分区表和分区表索引
--移动分区表
select distinct 'alter table '|| segment_name || ' move partition '|| partition_name || ' tablespacenew_tbs_nm;' from dba_extents where segment_type='TABLE PARTITION';

--移动分区索引
select distinct 'alter index '|| segment_name || ' rebuild partition '|| partition_name || ' tablespacenew_tbs_nm;' from dba_extents where segment_type='INDEX PARTITION';

 --移动BLOB字段
select 'alter table HGCOST.'|| TABLE_NAME || ' move lob('|| COLUMN_NAME||') store as (tablespace DATA);'
from dba_tab_columns where DATA_TYPE='BLOB' and owner='HGCOST';

--查询blob存储情况

SELECT L.INDEX_NAME,L.SEGMENT_NAME,I.TABLESPACE_NAME
FROM DBA_LOBS L,DBA_INDEXES I
WHERE L.TABLE_NAME=I.TABLE_NAME
  AND L.TABLE_NAME='WEB_FILEVIEW';