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');