oracle碎片整理

1、move碎片整理

1）

DECLARE
tmp_val VARCHAR2 (500);
BEGIN
FOR REC IN (SELECT TABLE_NAME FROM USER_TABLES )
LOOP
tmp_val:='ALTER TABLE '|| REC.TABLE_NAME ||' MOVE';
BEGIN
EXECUTE IMMEDIATE tmp_val;
DBMS_OUTPUT.ENABLE(buffer_size => null);
DBMS_OUTPUT.put_line (tmp_val);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || tmp_val || '!');
END;
END LOOP;
END;

2）

DECLARE
tmp_val VARCHAR2 (500);
BEGIN
FOR REC IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = 'T_ORDER' )
LOOP
tmp_val:='ALTER TABLE T_ORDER move partition '|| REC.partition_name ||'' ;
BEGIN
EXECUTE IMMEDIATE tmp_val;
DBMS_OUTPUT.ENABLE(buffer_size => null);
DBMS_OUTPUT.put_line (tmp_val);
EXCEPTION
WHEN OTH