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

在我们的数据仓库使用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.对其他用户赋权限



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值