如何还原oracle分区表,Oracle分区表迁移分区至历史表

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