如何正确Move Table，利用PLSQL可以批量处理

如何将一个表从一个表空间迁移到另外一个表空间。

 操作步骤：

1、评估迁移表占用空间大小以及新的表空间可用空间情况。

2、直接将表或者分区Move到新的表空间

3、设置表的tablespace属性为默认的新的表空间，确保新增分区能在新的表空间。

4、设置表的索引的存储属性表空间为对应的新的表空间（可选，根据具体情况处理）。

5、重建分区索引，全局索引

实际语法比较简单，对于批量方式，以下给出几个过程，用于批量处理：

--Move 分区表create or replace procedure p_mv_tabpart(pv_tname  in varchar2,                                         pv_destTS in varchar2) is    sqlStmnt varchar2(1024);    cursor pCur(vTname varchar2, vTspName varchar2) is        select table_name, partition_name          from user_tab_partitions         where table_name = vTname           and tablespace_name not like vTspName         order by partition_position desc;begin    for pRow in pCur(pv_tname, pv_destTS) loop        sqlStmnt := 'alter table ' || pRow.table_name || ' move partition ' ||                    pRow.partition_name || ' tablespace ' || pv_destTS;        execute immediate sqlStmnt;    end loop;end p_mv_tabpart;--设置表的tablespace 属性create or replace procedure p_set_attr_tab_ts(pv_tname  in varchar2,                                          pv_destTS in varchar2) is    sqlStmnt varchar2(1024);    cursor tCur(vTname varchar2) is        select table_name from user_part_tables where table_name = vTname;begin    for tRow in tCur(pv_tname) loop        sqlStmnt := 'alter table ' || tRow.table_name ||                    ' modify default attributes ' || ' tablespace ' ||                    pv_destTS;        execute immediate sqlStmnt;    end loop;end p_set_attr_tab_ts;--设置索引的tablespace 属性create or replace procedure p_set_attr_idx_ts(pv_tname  in varchar2,                                              pv_destTS in varchar2) is    sqlStmnt varchar2(1024);    cursor iCur(vTname varchar2) is        select index_name          from user_part_indexes         where index_name in (select index_name                                from user_indexes                               where table_name = vTname);begin    for iRow in iCur(pv_tname) loop        sqlStmnt := 'pv_tname index ' || iRow.index_name ||                    ' modify default attributes ' || ' tablespace ' ||                    pv_destTS;        execute immediate sqlStmnt;    end loop;end p_set_attr_idx_ts;--重建分区索引create or replace procedure p_mv_rb_part_idx(pv_tname  in varchar2,                                             pv_destTS in varchar2) is    sqlStmnt varchar2(1024);    cursor ndxCur(vTname varchar2, vTspName varchar2) is        select i.index_name index_name, ip.partition_name partition_name          from user_ind_partitions ip, user_indexes i         where i.index_name = ip.index_name           and i.table_name = vTname           and i.partitioned = 'YES'           and (ip.tablespace_name not like vTspName or               ip.status not like 'USABLE')         order by index_name, partition_name;begin    for ndxRow in ndxCur(pv_tname, pv_destTS) loop        sqlStmnt := 'alter index ' || ndxRow.index_name ||                    ' rebuild partition ' || ndxRow.partition_name ||                    ' tablespace ' || pv_destTS;        execute immediate sqlStmnt;    end loop;end p_mv_rb_part_idx;--重建全局索引create or replace procedure p_mv_rb_global_idx(pv_tname  in varchar2,                                               pv_destTS in varchar2) is    sqlStmnt varchar2(1024);    cursor ndxCur(vTname varchar2, vTspName varchar2) is        select index_name          from user_indexes         where table_name = vTname           and partitioned = 'NO'           and (tablespace_name not like vTspName or status like 'UNUSABLE')         order by index_name;begin    for ndxRow in ndxCur(pv_tname, pv_destTS) loop        sqlStmnt := 'alter index ' || ndxRow.index_name ||                    ' rebuild tablespace ' || pv_destTS;        execute immediate sqlStmnt;    end loop;end p_mv_rb_global_idx;