批量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自行查询。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |