Oracle分区表迁移分区至历史表

  • DBhanG

    2020-08-23 22:28:37
  • Oracle
  • 原创

记一次生产分区表迁移分区至历史表的流程

环境描述：
（所有数据已脱敏）
源表：

/ 源表信息 源表分区
表一 A1 A1_17,A1_18
表二 A2 A2_17,A2_18
表三 A3 A3_17,A3_18

历史表：（已经存在）

/ 历史表 历史表分区
表一 A1_HIS A1_HIS_17,A1_HIS_18
表二 A2_HIS A2_HIS_17,A2_HIS_18
表三 A3_HIS A3_HIS_17,A3_HIS_18

1.全备3张表
nohup expdp \’/ as sysdba\’ dumpfile=nowfull.dmp logfile=nowfull.log directory=hisclean tables=dbhang.A1,dbhang.FIN_ACCT_DTL,dbhang.FIN_VOU_INFO &

2.导出A1 2018 2017分区表
expdp dbhang/dbhang dumpfile=A1_1718.dmp directory=hisclean logfile=A1_1718.log tables=A1:A1_2017,A1:A1_2018;

3.获得A1索引语句

select ‘select dbms_metadata.get_ddl’ || q’[(‘INDEX’,’]’|| index_name || q’[‘,’dbhang’)]’ || ‘ from dual ‘ from dba_indexes where table_name = ‘A1’;

select dbms_metadata.get_ddl(‘INDEX’,’PK_A1’,’dbhang’) from dual

CREATE UNIQUE INDEX “dbhang”.”PK_A1” ON “dbhang”.”A1” (“B_C”, “BUS_NO”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “dbhang_INDEX”

select dbms_metadata.get_ddl(‘INDEX’,’A1_IDX2’,’dbhang’) from dual

CREATE INDEX “dbhang”.”A1_IDX2” ON “dbhang”.”A1” (“A_NO”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “dbhang_INDEX”

select dbms_metadata.get_ddl(‘INDEX’,’A1_IDX1’,’dbhang’) from dual

CREATE INDEX “dbhang”.”A1_IDX1” ON “dbhang”.”A1” (“B_C”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “dbhang_INDEX”

4.导入历史表
impdp dbhang/dbhang directory=hisclean dumpfile=A1_1718.dmp remap_table=A1:A1_2017:A1_HIS,A1:A1_2018:A1_HIS table_exists_action=append

5.验证数据一致
查看源表行数：
select count（*） from A1 partition（A1_2017）

COUNT（*）
20245959

select count（ ） from A1 partition（A1_2018）
COUNT（）
23072147

查看历史表行数：
select count（ ） from A1_HIS partition（A1_2017）
COUNT（）
20245959

select count（ ） from A1_HIS partition（A1_2018）
COUNT（）
23072147

6.删除源表分区
alter table A1 truncate partition (A1_2017)
alter table A1 truncate partition (A1_2018)

7.查看索引状态：
SQL> select index_name,index_type,owner,table_name,table_type,status from dba_indexes where table_name=’A1’;
INDEX_NAME INDEX_TYPE OWNER TABLE_NAME TABLE_TYPE STATUS

A1_IDX1 NORMAL dbhang A1 TABLE UNUSABLE
A1_IDX2 NORMAL dbhang A1 TABLE UNUSABLE
PK_A1 NORMAL dbhang A1 TABLE UNUSABLE

8.删除索引
drop index A1_IDX1
drop index A1_IDX2
drop index PK_A1

//删除主键时遇到的问题

// ORA-02429
//select owner,constraint_name,constraint_type,table_name from dba_constraints where table_name = ‘A1’ and owner=’dbhang’;
//alter table A1 drop constraint PK_A1

9.使用获取到的DDL语句重建索引（并记录时间）

重建A1_IDX1：

CREATE INDEX “dbhang”.”A1_IDX1” ON “dbhang”.”A1” (“B_C”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “dbhang_INDEX”

重建A1_IDX2：

CREATE INDEX “dbhang”.”A1_IDX2” ON “dbhang”.”A1” (“A_NO”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “dbhang_INDEX”

重建PK_A1

CREATE UNIQUE INDEX “dbhang”.”PK_A1” ON “dbhang”.”A1” (“B_C”, “B_NO”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “dbhang_INDEX”

9.验证索引状态：
SQL> select index_name,index_type,owner,table_name,table_type,status from dba_indexes where table_name=’A1’;
INDEX_NAME INDEX_TYPE OWNER TABLE_NAME TABLE_TYPE STATUS

PK_A1 NORMAL dbhang A1 TABLE VALID
A1_IDX2 NORMAL dbhang A1 TABLE VALID
A1_IDX1 NORMAL dbhang A1 TABLE VALID

10.重建历史表索引

//与重建源表索引方式相同

11.重新收集源表统计信息

exec dbms_stats.gather_table_stats(ownname=>’dbhang’,estimate_percent=>10,degree=>4,cascade=>TRUE,GRANULARITY=>’ALL’,tabname=>’A1’);

12.重新收集历史表统计信息

exec dbms_stats.gather_table_stats(ownname=>’dbhang’,estimate_percent=>10,degree=>4,cascade=>TRUE,GRANULARITY=>’ALL’,tabname=>’A1_HIS’);