plsql 导出分区_批量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_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自行查询。