ORACLE数据库表空间迁移--关于表和索引

下面是我写的一段程序，如果想自己生成代码，把声明的游标部分，放到SQLPLUS里执行一下，就可以了。

希望直接做这件事，那么修改文件中的两个参数，

   old_tbs := 'DATA';                                              --需要移出的表空间
   new_tbs := 'MONITOR_DATA';                       --准备移入的表空间

就可以运行了，然后查看看你定义的两个表空间内的对象是不是全的。注意::我的代码是把闪回区对象清空处理的（清回回收站），所以统计的时候，要注意这个情况！

/* Formatted on 2013/4/3 23:30:58 (QP5 v5.114.809.3010) */

/******************************************************************************
   PURPOSE:       移动表空间
   DATE CREATED:  2013-4-3
   CREATED BY:    黄越勇
******************************************************************************/

DECLARE
   --设置表可移动
   CURSOR c_moveable (
      old_tbs                 VARCHAR2
   )
   IS
      SELECT   DISTINCT
                  'alter table '
               || owner
               || '.'
               || segment_name
               || ' enable  row movement'
                  cmd
        FROM   dba_extents
       WHERE   tablespace_name = old_tbs AND segment_type = 'TABLE';

   --释放表空间,针对delete
   CURSOR c_shrink (
      old_tbs                 VARCHAR2
   )
   IS
      SELECT   DISTINCT
                  'alter table '
               || owner
               || '.'
               || segment_name
               || ' shrink space CASCADE'
                  cmd
        FROM   dba_extents
       WHERE   tablespace_name = old_tbs AND segment_type = 'TABLE';

   --移动表到新的表空间
   CURSOR c_move (
      old_tbs                 VARCHAR2,
      new_tbs                 VARCHAR2
   )
   IS
      SELECT   DISTINCT
                  'alter table '
               || owner
               || '.'
               || segment_name
               || ' move tablespace "'
               || new_tbs
               || '"'
                  cmd
        FROM   dba_extents
       WHERE   tablespace_name = old_tbs AND segment_type = 'TABLE';

   --重建索引到新表空间
   CURSOR c_index (
      old_tbs                 VARCHAR2,
      new_tbs                 VARCHAR2
   )
   IS
      SELECT   DISTINCT
                  'alter index '
               || owner
               || '.'
               || segment_name
               || ' rebuild tablespace "'
               || new_tbs
               || '"'
                  cmd
        FROM   dba_extents
       WHERE   tablespace_name = old_tbs AND segment_type = 'INDEX';

   --移动大对象到新的表空间
   CURSOR c_lob (
      old_tbs                 VARCHAR2,
      new_tbs                 VARCHAR2
   )
   IS
      SELECT      'alter table '
               || owner
               || '.'
               || table_name
               || ' move lob('
               || column_name
               || ')store as (tablespace '
               || new_tbs
               || ')'
                  cmd
        FROM   dba_lobs
       WHERE   segment_name IN (SELECT   segment_name
                                  FROM   dba_extents
                                 WHERE   tablespace_name = old_tbs);

   --清空表空间的闪回区
   CURSOR c_recycle (old_tbs VARCHAR2)
   IS
      SELECT   'purge table ' || owner || '."' || object_name || '"' cmd
        FROM   dba_recyclebin
       WHERE   type='TABLE' and ts_name= old_tbs;

   old_tbs   VARCHAR2 (30);
   new_tbs   VARCHAR2 (30);
BEGIN
   old_tbs := 'DATA';                               --需要移出的表空间
   new_tbs := 'MONITOR_DATA';                       --准备移入的表空间
   DBMS_OUTPUT.put_line ('--设置表可移动');

   FOR v_moveable IN c_moveable (old_tbs)
   LOOP
      DBMS_OUTPUT.put_line (v_moveable.cmd||';');
      BEGIN
         EXECUTE IMMEDIATE v_moveable.cmd;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
      END;
   END LOOP;

   DBMS_OUTPUT.put_line ('--释放表空间,针对delete');

   FOR v_shrink IN c_shrink (old_tbs)
   LOOP
      DBMS_OUTPUT.put_line (v_shrink.cmd||';');
      BEGIN
         EXECUTE IMMEDIATE v_shrink.cmd;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
      END;
   END LOOP;

   DBMS_OUTPUT.put_line ('--移动表到新的表空间');

   FOR v_move IN c_move (old_tbs, new_tbs)
   LOOP
      DBMS_OUTPUT.put_line (v_move.cmd||';');
      BEGIN
         EXECUTE IMMEDIATE v_move.cmd;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
      END;
   END LOOP;

   DBMS_OUTPUT.put_line ('--重建索引到新表空间');

   FOR v_index IN c_index (old_tbs, new_tbs)
   LOOP
      DBMS_OUTPUT.put_line (v_index.cmd||';');
      BEGIN
         EXECUTE IMMEDIATE v_index.cmd;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
      END;
   END LOOP;

   DBMS_OUTPUT.put_line ('--移动大对象到新的表空间');

   FOR v_lob IN c_lob (old_tbs, new_tbs)
   LOOP
      DBMS_OUTPUT.put_line (v_lob.cmd||';');
      BEGIN
         EXECUTE IMMEDIATE v_lob.cmd;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
      END;
   END LOOP;

   DBMS_OUTPUT.put_line ('--清空表空间的闪回区');

   FOR v_recycle IN c_recycle (old_tbs)
   LOOP
      DBMS_OUTPUT.put_line (v_recycle.cmd||';');
      BEGIN
         EXECUTE IMMEDIATE v_recycle.cmd;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
      END;
   END LOOP;
END;