数据仓库超级大表分区方式改变

在我们的数据仓库使用oracle其中有很多超级大表，这些表的分区方式基本都是range+hash分区或list+hash分区。使用时间字段进行range或list分区，再用用户ID进行子分区。不过最近有人反映有一张表select起来很慢，进过查看发现原来这个表只进行了list分区，没有进行hash子分区。讨论后决定对该表的分区方式进行改变：从list分区改为list+hash分区。操作过称如下：

 

1.前期准备

为了确定迁移策略先看看这个表有多大。

select owner, segment_name, sum(bytes) / 1024 / 1024 / 1024 size_G
  from dba_segments
 where owner = 'user'
   and segment_name = 'TABLE'

 

发现这个表有570多G。

这个表已经有二十多个分区，每个分区的记录数是将近10亿（是不是有点崩溃，这么大的表~）。

 

确定策略：为了不影响在线表的服务，先建一个预备表TABLE_NEW使用list+hash分区，然后一个分区一个分区的叫TABLE表的数据insert到TABLE_NEW表，再后将TABLE表改名为TABLE_OLD，将TABLE_NEW改成TABLE，最后对这个表给其他用户赋权限。

 

2. 倒出TABLE表的建表语句

 

通过查看TABLE表的建表DDL，得到建表语句。

 

查询数据库的object的DDL信息：

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','user') from dual;

修改建表语句并建TABLE_OLD表

 

3. 数据迁移

迁移程序日下

#!/usr/bin/bash
LOGFILE=TABLE.log

sqlplus user/password@DB <<EOD  >> ${LOGFILE}
set serverout on size 1000000
set timing on
alter session enable parallel dml;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

PROMPT Starting to insert into TABLE
select sysdate from dual;

declare
        v_commit        number;
        v_sql           varchar2(3000);
        error_msg       varchar2(300);
begin
        for rec in (select partition_name from dba_tab_partitions
                        where table_owner = 'user' and table_name = 'TABLE'

                        order by partition_name) loop
        begin
               v_sql :='insert /*+ append */into user.TABLE_NEW
                        (STATIS_MONTH,
                          ....

                          .....)                      
                  select STATIS_MONTH,
                    ......

                    ......

                    ......
               from user.TABLE  partition('||rec.partition_name||')';
          Execute Immediate v_sql;
              v_commit := Sql%Rowcount;
             
              commit;
             
              select sysdate from dual;
              dbms_output.put_line(rec.partition_name||' commit records'||v_commit);
         
          --如果一个分区出错，试下一个分区  
              exception
              when others then
                        error_msg := substr(sqlerrm, 1, 240);
                        dbms_output.put_line(||rec.partition_name||' - '||error_msg);
              end;
       
        end loop;
end;
exit;
EOD

 

 

确认每个分区的数据都迁移成功

 

4. --修改表名
 
sqlplus user/password@DB
rename TABLE to TABLE_OLD;
rename TABLE_NEW to TABLE;

 

5.对其他用户赋权限

转载 http://blog.csdn.net/xqy1522/article/details/5489823