日常维护脚本

1.迁移表空间相关

-- 提取当前用户下的表和索引获取move table或rebulid index的SQL

select decode( segment_type, 'TABLE',
                       segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'alter ' || segment_type || ' ' || segment_name ||
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
      chr(10) ||
      ' tablespace &1 ' || chr(10) ||
      ' storage ( initial ' || initial_extent || ' next ' ||
        next_extent || chr(10) ||
      ' minextents ' || min_extents || ' maxextents ' ||
        max_extents || chr(10) ||
      ' pctincrease ' || pct_increase || ' freelists ' ||
        freelists || ');'
  from user_segments,
       (select table_name, index_name from user_indexes )
 where segment_type in ( 'TABLE', 'INDEX' )
   and segment_name = index_name (+)
 order by 1, 2