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

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

| /    | 源表信息 | 源表分区    |
| :--- | -------- | :---------- |
| 表一 | 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');

ITPub博客

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

最新文章