Oracle表碎片整理与优化

-- 检查表的碎片情况并准备重新组织表
ANALYZE TABLE my_table COMPUTE STATISTICS;
 
-- 通过重新组织表来整理碎片
ALTER TABLE my_table MOVE;
 
-- 如果是分区表，则对每个分区进行碎片整理
ALTER TABLE my_partitioned_table MOVE PARTITION my_partition;
 
-- 使用DBMS_REDEFINITION进行在线表重定义，避免锁定
BEGIN
  DBMS_REDEFINITION.START_TABLE_REDEF_PREP(
    uname => 'MY_SCHEMA',
    tname => 'MY_TABLE',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
  
  DBMS_REDEFINITION.START_TABLE_REDEF_PREP(
    uname => 'MY_SCHEMA',
    tname => 'MY_TABLE',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
  
  DBMS_REDEFINITION.START_TABLE_REDEF_PREP(
    uname => 'MY_SCHEMA',
    orig_table => 'MY_TABLE',
    int_table_name => 'MY_TABLE_INTERIM'
  );
  
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname => 'MY_SCHEMA',
    orig_table => 'MY_TABLE',
    int_table_name => 'MY_TABLE_INTERIM',
    options_flag => DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS_ALL,
    copy_indexes => DBMS_REDEFINITION.COPY_INDEXES_AND_CONSTRAINTS
  );
  
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname => 'MY_SCHEMA',
    orig_table => 'MY_TABLE',
    int_table_name => 'MY_TABLE_INTERIM'
  );
  
  DBMS_REDEFINITION.FINISH_TABLE_REDEF_PREP(
    uname => 'MY_SCHEMA',
    orig_table => 'MY_TABLE',
    int_table_name => 'MY_TABLE_INTERIM'
  );
  
  COMMIT;
END;
/

这段代码展示了如何对Oracle表进行碎片整理。首先，使用ANALYZE TABLE来收集表的统计信息。接着，使用ALTER TABLE MOVE来重新组织表，对于分区表，可以对单个分区使用ALTER TABLE MOVE PARTITION。最后，如果需要，可以使用DBMS\_REDEFINITION包来在线重定义表，这个过程包括准备、复制数据、同步数据和完成重定义四个步骤。这种方法可以减少对表的长时间锁定，并且不需要额外的维护窗口。