批量move table（分区/非分区）的plsql

432qw

1、因前期TABLE的存储TABLESPACE设计不合理，或者需要对某个TABLESPACE进行维护管理，需要将该TABLESPACE中的对象转移，ORACLE提供了ALTER TABLE TABLE_NAME MOVE TABLESPACE NEW_TABLESPACE（需要有该表空间权限）的支持。

2、在MOVE TABLESPACE过程中需要注意含LOB字段的表不能直接MOVE，以及分区表的MOVE，在MOVE TABLESPACE之后，对应表的INDEX将会失效，需要重建。

3、为此写了一个PL/SQL程式进行批量MOVE（未经过严格的测试，请勿在生产库上使用，否则后果请自行承担）
PL/SQL如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144

DECLARE    v_sqltext1    VARCHAR2 (1000);    v_sqltext2    VARCHAR2 (1000);    v_sqltext3    VARCHAR2 (1000);    v_sqltext4    VARCHAR2 (1000);    v_sqltext5    VARCHAR2 (1000);    v_sqltext6    VARCHAR2 (1000);    v_sqltext7    VARCHAR2 (1000);    CURSOR tabletype_cur    IS       SELECT *         FROM dba_tables        WHERE owner = 'SCOTT' AND tablespace_name = 'USERS';    v_table_str   tabletype_cur%ROWTYPE;    CURSOR lob_cur (lobtable VARCHAR2)    IS       SELECT *         FROM dba_lobs        WHERE table_name = lobtable;    CURSOR index_cur (idx VARCHAR2)    IS       SELECT index_name         FROM dba_indexes        WHERE table_name = idx;    CURSOR part_index_cur (partidx VARCHAR)    IS       SELECT index_name         FROM dba_indexes        WHERE table_name = partidx       MINUS       SELECT index_name FROM dba_part_indexes; BEGIN    OPEN tabletype_cur;    LOOP       FETCH tabletype_cur INTO v_table_str;       IF v_table_str.partitioned = 'NO'                               --是否为分区表       THEN          FOR movelob IN lob_cur (v_table_str.table_name)           --是否含有LOB字段          LOOP             v_sqltext1 :=                   'ALTER TABLE SCOTT.'                || movelob.table_name                || ' MOVE LOB('                || movelob.column_name                || ') STORE AS '                || movelob.segment_name                || ' (TABLESPACE TEST)';             EXECUTE IMMEDIATE v_sqltext1;          --DBMS_OUTPUT.put_line (v_sqltext1);          END LOOP;          v_sqltext2 :=                'ALTER TABLE SCOTT.'             || v_table_str.table_name             || ' MOVE TABLESPACE TEST';          --DBMS_OUTPUT.put_line (v_sqltext2);          EXECUTE IMMEDIATE v_sqltext2;                                 --移动普通表          FOR moveidx IN index_cur (v_table_str.table_name)  --将移动之后表的对应INDEX重建          LOOP             v_sqltext3 :=                   'ALTER INDEX SCOTT.'                || moveidx.index_name                || ' REBUILD ONLINE TABLESPACE TEST';             EXECUTE IMMEDIATE v_sqltext3;          --DBMS_OUTPUT.put_line (v_sqltext3);          END LOOP;       END IF;       IF v_table_str.partitioned = 'YES'       THEN                          --分区表分区的移动,暂不考虑有子分区的情况,若MOVE子分区将报ORA-14257          FOR part             IN (SELECT partition_name                   FROM dba_tab_partitions                  WHERE     subpartition_count = 0                        AND table_name = v_table_str.table_name)          LOOP             v_sqltext4 :=                   'ALTER TABLE SCOTT.'                || v_table_str.table_name                || ' MOVE PARTITION '                || part.partition_name                || 'TABLESPACE TEST';             EXECUTE IMMEDIATE v_sqltext4;             --DBMS_OUTPUT.put_line (v_sqltext4);             FOR movepartidx1                IN (SELECT index_name                      FROM dba_ind_partitions                     WHERE     subpartition_count = 0                           AND partition_name = part.partition_name)             LOOP                v_sqltext5 :=                      'ALTER INDEX '                   || movepartidx1.index_name                   || ' REBUILD PARTITION '                   || part.partition_name                   || ' TABLESPACE TEST';                EXECUTE IMMEDIATE v_sqltext5;              --将移动之后分区表的分区INDEX重建             END LOOP;          END LOOP;          v_sqltext6 :=                'ALTER TABLE SCOTT.'             || v_table_str.table_name             || 'MODIFY DEFAULT ATTRIBUTES TABLESPACE TEST';          EXECUTE IMMEDIATE v_sqltext6;          --DBMS_OUTPUT.put_line (v_sqltext6);          FOR movepartidx2 IN part_index_cur (v_table_str.table_name) --将移动之后分区表的全局INDEX重建          LOOP             v_sqltext7 :=                   'ALTER INDEX SCOTT.'                || movepartidx2.index_name                || ' REBUILD ONLINE TABLESPACE TEST';             EXECUTE IMMEDIATE v_sqltext7;          --DBMS_OUTPUT.put_line (v_sqltext7);          END LOOP;       END IF;    END LOOP;    CLOSE tabletype_cur; EXCEPTION    WHEN OTHERS    THEN       DBMS_OUTPUT.put_line (SQLERRM); END; /

总结：该PL/SQL主要是通过判断一个用户下的表是否为分区表进行move，并根据该表的对象类型来做处理，存在过多的逻辑判断，比较繁琐，且是根据表类型来进行的，有比较大的局限性，后面考虑从需要move的tablespace方向出发，对tablespace上的对象分类进行move。如：A）先将该tablespace中的含LOB字段的表move,并重建索引。B）之后将该tablespace中的分区表进行move，包含分区，子分区，并重建索引，包含全局索引，分区索引，含子分区索引。C）在之后查看该tablespace中是否有LONG字段的表，有就进行处理。D) 之后对剩下的普通表进行move，并重建索引，就可将整个tablespace中的对象move。最后，对于move table的作用和影响请结合shrink table自行查询。