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_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自行查询。
转载于:https://blog.51cto.com/wyzwl/1837477