批量迁移oracle表存储

fenghzy

　　场景：网友marine遇到问题，要求将某用户的表从表空间A移动到表空间B，用户表的个数在1000+以上
　　
分析1：首先可以确定的是针对用户单张表移动表空间使用alter table table_name remove tablespace tablespace_name这种DDL语句实现；
　　
其次需要查dba_tables视图找出改用户在A表空间上的表名称，然后将值保存为变量传递给for循环；
　　
最后执行动态SQL，在PL/SQL中不可能直接执行DDL语句，因而需要使用execute immediate的方式执行动态SQL；
　　

　　
分析2：当然也可以去拼凑一个sql脚本，然后执行脚本达到这个效果,但执行效率肯定不如前者
　　

• SQL> set echo off
  
• SQL> set feedback off
  
• SQL> set heading off
  
• SQL> spool /home/oracle/move.sql
  
• SQL> select 'alter table hr.' ||table_name || ' move tablespace TBS_APPLE ;'
  
•   2  from dba_tables where owner='HR' and tablespace_name='EXAMPLE';
  
• 
  
• [oracle@orcl ~]$ cat move.sql
  
• alter table hr.REGIONS move tablespace TBS_APPLE ;
  
• alter table hr.LOCATIONS move tablespace TBS_APPLE ;
  
• alter table hr.DEPARTMENTS move tablespace TBS_APPLE ;
  
• alter table hr.JOBS move tablespace TBS_APPLE ;
  
• alter table hr.EMPLOYEES move tablespace TBS_APPLE ;
  
• alter table hr.JOB_HISTORY move tablespace TBS_APPLE ；
  

　　

　　模拟相关场景
　　
步骤一：新建表空间，查出用户所拥有的在A表空间上的表，这里的用户以HR为例，表空间以example为例
　　

• SQL> create tablespace tbs_apple datafile '/u01/app/oracle/oradata/orcl/tbs_apple01.dbf'
  
•    2 size 10M autoextend on next  10M maxsize 1G
  
•    3 extent management local segment space management auto;
  
• 
  
• SQL> select table_name from dba_tables where owner='HR' and tablespace_name='EXAMPLE';
  
• TABLE_NAME
  
• ------------------------------
  
• REGIONS
  
• LOCATIONS
  
• DEPARTMENTS
  
• JOBS
  
• EMPLOYEES
  
• JOB_HISTORY
  
• 6 rows selected.
  

　　

　　步骤二：使用变量实现
　　

• SQL> declare
  
•   2  v_1 varchar2(200);
  
•   3  begin
  
•   4     select table_name into v_1 from dba_tables where owner='HR' and tablespace_name='EXAMPLE';
  
•   5       begin
  
•   6         for i in v_1
  
•   7         loop
  
•   8           execute immediate 'alter table  hr.'||v_1 || ' move tablespace example';
  
•   9         end loop;
  
• 10       end;
  
• 11  end;
  
• 12  /
  
•        for i in v_1
  
•                 *
  
• ERROR at line 6:
  
• ORA-06550: line 6, column 17:
  
• PLS-00456: item 'V_1' is not a cursor
  
• ORA-06550: line 6, column 8:
  
• PL/SQL: Statement ignored
  

　　

　　以上报错，说明在PL/SQL中不可能直接将变量变成散列放进for循环，需要使用游标；而在shell脚本中可以轻易实现这点！
　　
步骤三:使用游标
　　
先从百度上搜索下游标的概念：
　　
游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
　　

• SQL> declare
  
•   2      v_1   varchar2(200);
  
•   3    cursor c_1 is
  
•   4          select table_name from dba_tables where owner='HR' and tablespace_name='EXAMPLE';
  
•   5  begin
  
•   6      open c_1;
  
•   7      fetch c_1 into v_1;
  
•   8       while c_1%found
  
•   9        loop
  
• 10         execute immediate 'alter table  hr.'||v_1 || ' move tablespace tbs_apple';
  
• 11         fetch c_1 into v_1;
  
• 12        end loop;
  
• 13      close c_1;
  
• 14* end;
  
• 15  /
  
• PL/SQL procedure successfully completed.
  

　　

　　步骤四：验证结果
　　

• SQL> select table_name from dba_tables where owner='HR' and tablespace_name='TBS_APPLE';
  
• 
  
• TABLE_NAME
  
• ------------------------------
  
• REGIONS
  
• LOCATIONS
  
• DEPARTMENTS
  
• JOBS
  
• EMPLOYEES
  
• JOB_HISTORY
  
• 
  
• 6 rows selected.
  

　　

　　注意事项：迁移完用户的表后，要检查下表的索引是否需要迁移，另外要注意修改用户的默认永久表空间，根据需要来修改，否则用户新建的表又会存放到旧的表空间上；用户拥有的其他对象，例如视图，触发器，过程，包这些都存储在数据字典上，不需要进行迁移！