生产库大表拆分的需求案例分析

测试针对表bj_container实施，其他两个表类似。

方案测试流程：
1 创建历史表空间tbs_HIS，给用户授权
create tablespace tbs_HIS DATAFILE '/oradata2/bj111/tbs_HIS01.dbf' size 20g;
alter user user1 quota unlimited on tbs_HIS;

2 使用数据泵导出bj_container相关年份的数据。下面指令需要修改相应修改，这里导出索引，元数据和统计信息，但是数据只是2021年的
导出2021年数据
expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2021.dmp tables=user1.bj_container QUERY=\"WHERE bj_date like \'2021\%\'\"

导出其他年份数据。不导出索引，后续创建索引，这里考虑导入时的索引创建报错，对于21年前的数据我们单独按照年导出，并且不导出索引。

###expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2015.dmp tables=user1.bj_container content=data_only exclude=index QUERY=\"WHERE bj_date like \'2015\%\'\"

expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2015.dmp tables=user1.bj_container exclude=index QUERY=\"WHERE bj_date like \'2015\%\'\"
expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2016.dmp tables=user1.bj_container exclude=index QUERY=\"WHERE bj_date like \'2016\%\'\"
expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2017.dmp tables=user1.bj_container exclude=index QUERY=\"WHERE bj_date like \'2017\%\'\"
expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2018.dmp tables=user1.bj_container exclude=index QUERY=\"WHERE bj_date like \'2018\%\'\"
expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2019.dmp tables=user1.bj_container exclude=index QUERY=\"WHERE bj_date like \'2019\%\'\"
expdp \'/as sysdba\' directory=dir dumpfile=bj_container_2020.dmp tables=user1.bj_container exclude=index QUERY=\"WHERE bj_date like \'2020\%\'\"

3 更改表名字，这里我们讲改表明，使得21年数据导入时，不会冲突，同时消除了不处理21年数据而使用删除数据的方法导致系统压力，因为删除的数据量有2.5亿左右
alter table user1.bj_container rename to user1.bj_container_old;

删除索引，为了避免导入21年数据导致的索引冲突。
drop index user1.IND_bj_container_2;
drop index user1.IND_bj_container_1;


4 导入表 （先导入2021年数据)
2021年数据，此时会创建一个原始表，但是数据只有21年，索引等对象自动导入，操作简单。

impdp \'/as sysdba\' directory=dir dumpfile=bj_container_2021.dmp

其他年份数据，这里只是导入了数据，并且做了rename，表空间做了映射，是出于管理方便。
impdp \'/as sysdba\' directory=dir dumpfile=bj_container_2015.dmp remap_table=bj_container:bj_container_2015 remap_tablespace=tbs:tbs_HIS
impdp \'/as sysdba\' directory=dir dumpfile=bj_container_2016.dmp remap_table=bj_container:bj_container_2016 remap_tablespace=tbs:tbs_HIS
impdp \'/as sysdba\' directory=dir dumpfile=bj_container_2017.dmp remap_table=bj_container:bj_container_2017 remap_tablespace=tbs:tbs_HIS
impdp \'/as sysdba\' directory=dir dumpfile=bj_container_2017.dmp remap_table=bj_container:bj_container_2018 remap_tablespace=tbs:tbs_HIS
impdp \'/as sysdba\' directory=dir dumpfile=bj_container_2019.dmp remap_table=bj_container:bj_container_2019 remap_tablespace=tbs:tbs_HIS
impdp \'/as sysdba\' directory=dir dumpfile=bj_container_2020.dmp remap_table=bj_container:bj_container_2020 remap_tablespace=tbs:tbs_HIS

5
创建索引 由于历史表需要查询，所以之前的索引按照原来的样子重建，索引定义是通过dbms_medatada.get_ddl获得，比较准确。
表bj_container的拆分表创建索引
CREATE INDEX "user1"."IND_bj_container_1_2015" ON "user1"."bj_container_2015" ("bj_date") ;
CREATE INDEX "user1"."IND_bj_container_1_2016" ON "user1"."bj_container_2016" ("bj_date") ;
CREATE INDEX "user1"."IND_bj_container_1_2017" ON "user1"."bj_container_2017" ("bj_date") ;
CREATE INDEX "user1"."IND_bj_container_1_2018" ON "user1"."bj_container_2018" ("bj_date") ;
CREATE INDEX "user1"."IND_bj_container_1_2019" ON "user1"."bj_container_2019" ("bj_date") ;
CREATE INDEX "user1"."IND_bj_container_1_2020" ON "user1"."bj_container_2020" ("bj_date") ;

CREATE INDEX "user1"."IND_bj_container_2_2015" ON "user1"."bj_container_2015" ("HPID") ;
CREATE INDEX "user1"."IND_bj_container_2_2016" ON "user1"."bj_container_2016" ("HPID") ;
CREATE INDEX "user1"."IND_bj_container_2_2017" ON "user1"."bj_container_2017" ("HPID") ;
CREATE INDEX "user1"."IND_bj_container_2_2018" ON "user1"."bj_container_2018" ("HPID") ;
CREATE INDEX "user1"."IND_bj_container_2_2019" ON "user1"."bj_container_2019" ("HPID") ;
CREATE INDEX "user1"."IND_bj_container_2_2020" ON "user1"."bj_container_2020" ("HPID") ;

6 导入后的查询确认

确认表
select table_name,tablespace_name from dba_tables where table_name like 'bj_container%' or table_name like 'HP_CAR%' OR table_name like 'HP_BASIC%' ORDER BY 1;

确认索引
col owner for a20
col table_name for a20
col index_name for a30
set line 120
select owner,table_name,index_name,status from dba_indexes where table_name=upper('bj_container')


原始表索引信息比对,下面查原库
select owner,table_name,index_name,status from dba_indexes where table_name in (upper('bj_container'))


7 一天之后业务确认无误，删除历史表
drop table user1.bj_container;