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

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如下：

DECLARE
v_sqltext1VARCHAR2(1000);
v_sqltext2VARCHAR2(1000);
v_sqltext3VARCHAR2(1000);
v_sqltext4VARCHAR2(1000);
v_sqltext5VARCHAR2(1000);
v_sqltext6VARCHAR2(1000);
v_sqltext7VARCHAR2(1000);

CURSORtabletype_cur
IS
SELECT*
FROMdba_tables
WHEREowner='SCOTT'ANDtablespace_name='USERS';

v_table_strtabletype_cur%ROWTYPE;

CURSORlob_cur(lobtableVARCHAR2)
IS
SELECT*
FROMdba_lobs
WHEREtable_name=lobtable;

CURSORindex_cur(idxVARCHAR2)
IS
SELECTindex_name
FROMdba_indexes
WHEREtable_name=idx;

CURSORpart_index_cur(partidxVARCHAR)
IS
SELECTindex_name
FROMdba_indexes
WHEREtable_name=partidx
MINUS
SELECTindex_nameFROMdba_part_indexes;
BEGIN
OPENtabletype_cur;

LOOP
FETCHtabletype_curINTOv_table_str;

IFv_table_str.partitioned='NO'--是否为分区表
THEN
FORmovelobINlob_cur(v_table_str.table_name)--是否含有LOB字段
LOOP
v_sqltext1:=
'ALTERTABLESCOTT.'
||movelob.table_name
||'MOVELOB('
||movelob.column_name
||')STOREAS'
||movelob.segment_name
||'(TABLESPACETEST)';

EXECUTEIMMEDIATEv_sqltext1;
--DBMS_OUTPUT.put_line(v_sqltext1);
ENDLOOP;

v_sqltext2:=
'ALTERTABLESCOTT.'
||v_table_str.table_name
||'MOVETABLESPACETEST';

--DBMS_OUTPUT.put_line(v_sqltext2);

EXECUTEIMMEDIATEv_sqltext2;--移动普通表

FORmoveidxINindex_cur(v_table_str.table_name)--将移动之后表的对应INDEX重建
LOOP
v_sqltext3:=
'ALTERINDEXSCOTT.'
||moveidx.index_name
||'REBUILDONLINETABLESPACETEST';

EXECUTEIMMEDIATEv_sqltext3;
--DBMS_OUTPUT.put_line(v_sqltext3);
ENDLOOP;
ENDIF;

IFv_table_str.partitioned='YES'
THEN--分区表分区的移动,暂不考虑有子分区的情况,若MOVE子分区将报ORA-14257
FORpart
IN(SELECTpartition_name
FROMdba_tab_partitions
WHEREsubpartition_count=0
ANDtable_name=v_table_str.table_name)
LOOP
v_sqltext4:=
'ALTERTABLESCOTT.'
||v_table_str.table_name
||'MOVEPARTITION'
||part.partition_name
||'TABLESPACETEST';

EXECUTEIMMEDIATEv_sqltext4;

--DBMS_OUTPUT.put_line(v_sqltext4);
FORmovepartidx1
IN(SELECTindex_name
FROMdba_ind_partitions
WHEREsubpartition_count=0
ANDpartition_name=part.partition_name)
LOOP
v_sqltext5:=
'ALTERINDEX'
||movepartidx1.index_name
||'REBUILDPARTITION'
||part.partition_name
||'TABLESPACETEST';

EXECUTEIMMEDIATEv_sqltext5;--将移动之后分区表的分区INDEX重建
ENDLOOP;
ENDLOOP;

v_sqltext6:=
'ALTERTABLESCOTT.'
||v_table_str.table_name
||'MODIFYDEFAULTATTRIBUTESTABLESPACETEST';

EXECUTEIMMEDIATEv_sqltext6;

--DBMS_OUTPUT.put_line(v_sqltext6);

FORmovepartidx2INpart_index_cur(v_table_str.table_name)--将移动之后分区表的全局INDEX重建
LOOP
v_sqltext7:=
'ALTERINDEXSCOTT.'
||movepartidx2.index_name
||'REBUILDONLINETABLESPACETEST';

EXECUTEIMMEDIATEv_sqltext7;
--DBMS_OUTPUT.put_line(v_sqltext7);
ENDLOOP;
ENDIF;
ENDLOOP;

CLOSEtabletype_cur;
EXCEPTION
WHENOTHERS
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自行查询。

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

猜你在找的Oracle相关文章