Oracle大数据量迁移

prompt 生成历史表，使用nologging
create table his_test nologging as select * from test;

prompt 检验新旧表的数据量是否一致
select count(1) from test;
select count(1) from his_test;

prompt 禁用外键约束
alter table TEST_MX disable constraint FK_MX_REF_TEST;

prompt 清空旧表，保留索引、注释、权限等
truncate table test;

prompt 禁用索引，使用unusable，而不使用disable
alter index IDX_TEST_NAME unusable;

prompt 使用insert into … select 迁移数据
insert into test (id,name,age)
select id,
nvl((select nickname from other_table a where a.name=t.name),t.name),
age
from his_test t;

prompt 生成重建分区索引的sql语句
select ‘alter index ’ || index_owner || ‘.’ ||index_name ||’ rebuild partition ’ || PARTITION_NAME || ’ parallel;’
from dba_ind_partitions
where index_owner = ‘ORCL’
AND INDEX_NAME like ‘IDX_TEST%’;